Excel’s IF function makes If…Then… decisions in your workbook formulas. After you learn SUM, IF is the next most important function every consultant should learn. The IF function and its variations are a must if you want to build powerful worksheets that go beyond simple math or financial calculations.
If you already consider yourself an IF function expert, you might want to check Tips 7, 9, 13, 14, and 19 for more powerful tips.
“In the beginner’s mind there are many possibilities, but in the experts there are few.”
Shunryu Suzuki (1905-1971)
Excel’s IF function variations also make decisions in your worksheets, for example, SUMIF, COUNTIF, and AVERAGEIF calculate on values in a range that match a criteria you specify. SUMIFS, COUNTIFS, and AVERAGEIFS calculate given multiple criteria. Another decider is IFERR or IFERROR. They decide which result to produce based on whether the formula produces an error. When you need to choose from multiple possible results of a decision, then use the CHOOSE function. And you can even add additional decision logic to IF functions by incorporating AND, OR, and NOT.
Help another Excel user by passing this on to them. Thanks!
Use the IF function to make decisions between which results to calculate in Excel formulas. The IF function uses the syntax,
=IF (logical_test, [value_if_true], [value_if_false])
IF must have a logical test, but the TRUE and FALSE arguments are optional. The logical_test is a value, reference, or calculation that returns a TRUE or FALSE. The logical_test must evaluate to either TRUE or FALSE, for example, $A$5>50. If the logical_test you specify is TRUE, then the value_if_true is returned. If the logical condition you specify is FALSE, then the function returns value_if_false. The TRUE/FALSE results can be a static value, a cell reference, or a formula.
Click here to read Microsoft’s basic definition of the IF function.
Click here to go to a free IF function tutorial from the Goodwill Community Foundation.
Excel has many different functions that use IF functionality to make calculated decisions. Knowing which function is best for your situation can save you a tremendous amount of time and frustration. Most of these are introduced in the tips in this blog.
IF tests a TRUE/FALSE logical criteria to decide between two results. If you need to chose between more than two results, use nested IF functions or use the IFS function. Both of these are in later tips.
IFS tests multiple TRUE/FALSE logical criteria to decide between two results. The multiple criteria must ALL be TRUE for the value_if_true to be the result. Using IFS is much easier than using nested IF functions for multiple criteria. IFS is only available in Excel 2016, Excel Online, Excel Andriod.
SUMIF, COUNTIF, AVERAGEIF
Sums, counts, or averages values in a range that meet a criteria.
SUMIFS, COUNTIFS, AVERAGEIFS
Sums, counts, or averages values in a range where all of multiple criteria are true.
IFERROR and IFNA are used to manage calculations that might produce an error. If a formula results in an error, then a message or alternative calculation can be used.
CHOOSE selects between multiple results based upon a calculated number.
VLOOKUP uses a term you give it to decide which item from a list it will retrieve.
Nest IF functions within other IF functions when you need results from more than two decisions. For example, in this figure IF functions are nested so they evaluate four different results depending upon the values in column F.
If $F6 is less than 500, then the result is 0.03, if not, the next “nested IF” is evaluated.
If $F6 in the second IF is less than 2000 (but 500 or greater), then the result is 0.05. If not, then the third IF is evaluated.
If $F6 in the third IF is 6000 (but 2000 or greater), then result is 0.09. If not, the result is .09.
Be careful when you nest IF functions. The IF functions must be in the correct logical order. The first logical condition that tests TRUE stops the function and returns result for that TRUE. If conditions are not in the correct order the IF function can produce an incorrect result. For example, in this formula,
the IF function produces incorrect results for values in $F6 between 500 and 1,999 because the $F6<6000 will evaluate as TRUE before the third IF function is evaluated.
Beware: for every left parenthesis, there is a corresponding right parenthesis. Pairs of parenthesis must match and contain a valid Excel syntax or the IF function will produce an error. See Tips 18 and 19 for tips on troubleshooting nested IF statements.
Click here for more information from Microsoft Support on nested IF formulas.
Excel 2003 and earlier only support seven (7) nested IF functions. Excel 2007 and newer versions support up to 64 nested IFs.
As a general recommendation, it is difficult to create or troubleshoot nested IF statements with more than three to seven levels. If you have a large number of logical conditions to test you should find another method.
Alternative solutions to having many nested IF functions are,
A blank cell is the result of a TRUE condition when you enter no argument as the value_if_true or value_if_false, for example,
While this is easy to type, it makes the formula difficult to read and troubleshoot. It is better to enter a zero. For example,
If you want the results to be blank, so the cell contains nothing, then use two quotes with nothing between them. For example,
=IF($A$5<50,””,”Fifty or greater”)
Long or complicated nested IF functions are not only difficult to understand then can be even more difficult to troubleshoot. To make long nested IF functions easier you can put both criteria and results in a table of cells, then reference cells in the table from inside the IF function. Of course, cells that contain a logical test must result in a TRUE or FALSE.
This figure from the “Nested IF w Criteria Table” sheet of the downloadable Excel tutorial is has the criteria limits in cells K6:K9 and the results in cells L6:L9.
Nested IF statements or any complex formula are easier to read if you put line breaks to separate segments of the formula. These line breaks appear in the formula bar at the top of the worksheet, but do not appear in the cell or in the formula. This figure shows a nested IF statement with each piece separated by a line break. This example is on the “Nested IF w Line Breaks” worksheet in the Excel tutorial worksheet.
To break a formula into separate lines, position the insertion point where you want the line break, then press Alt+Enter. On a Mac, press Ctrl+Opt+Enter. You can insert multiple line breaks in a statement.
This is a great way of making any long formula easier to read and troubleshoot. For other great tips on troubleshooting check these links in www.CriticaltoSuccess.com.
Normally the formula bar shows only a single line, however, you can drag the lower line of the formula bar down so that it shows formulas with multiple lines, like that in the figure.
Tip 19 shows how to evaluate each segment of a formula, step-by-step. This works nicely with line breaks to make the operation of long formulas easier to understand.
Left and right parenthesis in nested IF statements must match or the IF statement will return an error. To fix the problem of missing parenthesis checking that pairs of left and right parenthesis enclose functional formulas that can be resolved.
Matching pairs of parenthesis have matching colors. This makes pairs stand out, but sometimes you need more.
To bold matching parenthesis,
1. Click in the formula bar at the beginning of the IF statement.
2. Press the right-arrow key to move the insertion point until it is before the first left parenthesis, “(“.
3. As you press the right-arrow key to move across the left-parenthesis you will see both the left and right-hand matching parenthesis flash bold. These are the parenthesis that Excel thinks match, but they may not be correct.
4. Continue moving the insertion point across parenthesis while watching for matching pairs that flash bold. If a parenthesis do not enclose a valid formula segment, move, add, or delete the incorrect parenthesis.
For long formulas, start at the farthest left and work right while looking for a match. If you do not find the mismatch, then start with an innermost pair that you know is correct and begin working outward.
If you are still unable to find the incorrect parenthesis you may want to use Tip 7 to break the formula into segments, Tip 18 to see if segments within parenthesis calculate, and Tip 19 to step through the logic and calculations.
Use the new IFS function to create more easily understood IF functions with up to 127 different criteria. IFS is available in Excel 2016, Excel Online, Excel Mobile, or Excel for Android. It is not backward compatible to early Excel versions.
The IFS function is easier to understand because each set of logic conditions and TRUE and FALSE results are together rather than separated as they are with nested IF.
Click here to learn more about the IFS function at Microsoft’s Office support site.
You can test for many worksheet and cell conditions using IF. Use for the logical_condition any of the Excel functions that test a cell’s properties or contents. For example, the following can be used as logical conditions and return TRUE when the condition is met.
Cell is blank
Cell is not blank
Cell contains text
Cell does not contain text
Cell contains numbers
Cell does not contain numbers
Versions of Excel prior to 2007 had a confusing way of handling errors. In these early version the way to handle an error looked like this,
If the formula produced an error, the ISERROR resulted in TRUE, so the error_result appeared in the cell. The error_result was often a text warning or an alternative calculation.
If the formula did not produce an error, ISERROR resulted in FALSE, so the last argument, the formula was the final result. This meant the formula had to be written twice and calculated twice.
In Excel 2007 and more recent versions, the IFERROR function is more elegant. The syntax is,
You enter the formula one time. If it does not produce an error, then the IFERROR result is the formula result. If the formula does produce an error, then error_result is the result.
SUMIF, COUNTIF, or AVERAGEIF to calculate a result on a range where only cells meeting a criterion are included in the calculation. You also can calculate on one range while matching criteria against cells in a different range. The syntax is,
=SUMIF(range, criteria, [sum_range])
If you do not specify the sum_range argument, then the calculation is on the first range argument.
The Static & Dynamic SUMIF COUNTIF tab in the Excel tutorial file give examples of how to use a criterion embedded within the function or a dynamic criterion where the criteria is in a cell making it easy to change.
Use SUMIFS, COUNTIFS, or AVERAGEIFS to calculate over a range where multiple criteria are met. All criteria must be TRUE.
SUMIFS syntax is,
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Click here for the description of SUMIFS at Microsoft Support.
Use SUMIFS, COUNTIFS, or AVERAGEIFS to calculate on a range where all cells included in the calculation must be TRUE for multiple criteria.
Using multiple criteria and putting the criteria in cells that can be referenced allows users to enter any date range they want. You can make this even better by giving your users drop-down lists to select choices from. For example, you could add a third criterion and a drop-down to give users the ability to select an Item to calculate on.
Calculating the sum, average, and count across a range for a date range is shown in the Date Range SUM-COUNT-AVERAGEIF tab of the downloadable Excel example file. In this example, SUMIF adds all total sales in $H$6:$H$37 for those rows where the date is equal to or greater than 1/1/2017 and less than or equal to 6/30/2017.
Specifying an entire column, for example, A:A, inside a SUMIF(S), COUNTIF(S), or AVERAGEIF(S) is an easy way to enter the range to be calculated on or the range containing the criteria values. However, that can slow performance by making Excel calculate across tens of thousands of unnecessary cells. If you have other data in the column it might also cause errors.
A more professional approach is to use dynamic range names to specify the range of calculation or criteria. Dynamic range names are range names that automatically adjust their size as a filled range of data expands or contracts.
Click here “Creating and Checking Dynamic Named Ranges using Excel’s OFFSET Function” to learn more about dynamic range names.
Use the CHOOSE function to make a choice between multiple results. The CHOOSE syntax is,
=CHOOSE(index_num, value1, [value2], …)
The index_num must result in a number from 1 to 254. If it results in 1, then value1 is returned. If it results in 2, then value2 is returned and so forth.
Click here to learn more about CHOOSE at the Microsoft Support site.
Another way to make decisions between many choices is by using VLOOKUP. VLOOKUP will use a lookup_value you specify or calculate to lookup a result from a table or list. Using the optional [range_lookup] argument with VLOOKUP gives you results for exact matches or approximate matches.
Click here “Using VLOOKUP to retrieve data from an Excel table” to learn more about VLOOKUP and to download an Excel tutorial worksheet.
Click here “Top 21 VLOOKUP Tips and Tricks” to learn tips and tricks about manipulating VLOOKUP.
If you are testing an IF function and want to see whether the logical arguments evaluate to TRUE or FALSE, select the entire argument inside the function. The selection must be able to be calculated as a standalone formula. Then, press the F9, Calculate, key. The selected segment’s result will replace the selection.
Press Esc to return to the original function. If you accidentally press Enter and enter the formula, then press Ctrl+Z to go back.
To watch Excel go through a nested IF function or any formula, and see the results of each segment being calculated, use Excel’s Evaluate Formula feature.
Select the cell with the nested IF, in this case, I16 of the Excel tutorial file. On the Formula tab, in the Formula Auditing group, click Evaluate Formula to display the Evaluate Formula dialog box shown above. Click the Evaluate button to step through each section of the nested IF to see if it works correctly.