19 Top Tips on Excel’s IF Functions and Formulas

Excel

Excel’s IF function makes If…Then… decisions in your workbook formulas. 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.

Use Evaluate Formula to watch each segment of a formula calculate.

Use Evaluate Formula to watch each segment of a formula calculate.

“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.

Download the sample Excel tutorial worksheet.

Help another Excel user by passing this on to them. Thanks!

1. A Quick IF Function Tutorial

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.

2. Excel has Many IF-Like Decision-Making Functions

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

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

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, IFNA

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

CHOOSE selects between multiple results based upon a calculated number.

VLOOKUP

VLOOKUP uses a term you give it to decide which item from a list it will retrieve.

3. Nest IF Functions to Handle Multiple Decision Levels

Nesting IF functions enables multiple decisions, but they can be difficult to understand.

Nesting IF functions enables multiple decisions, but they can be difficult to understand.

Download the sample Excel tutorial worksheet.

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<500,0.03,IF($F6<2000,0.05,IF($F6<6000,0.07,0.09)))

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,

=IF($F6<500,0.03,IF($F6<6000,0.05,IF($F6<2000,0.07,0.09)))

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.

4. Excel 2007 and Newer Versions Support up to 64 Nested IF Levels

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,

  • Reduce complexity by putting IF arguments into cells and then referencing the cells as described in Tip 6.
  • Use IFS when you have multiple criteria and results. IFS is only available in the newest Excel versions as described in Tip 9.
  • Use the multiple criteria capability of SUMIFS, COUNTIFS, or AVERAGEIFS functions as described in Tip 13 and 14.
  • Using a VLOOKUP with a table of criteria as described in Tip 17.

5. Specify Zero or Blank Results

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,

=IF($A$5<50,,.7)

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($A$5<50,0,.7)

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”)

6. Make Long or Complex IF Functions Easier to Understand by Putting Arguments in Cells

Make nested IF functions easier to understand by putting criteria and results in a table on the sheet.

Download the sample Excel tutorial worksheet.

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.

7. Add Line Breaks to Nested IF Formulas to Make Them Easier to Read

Use a line break in the formula bar to separate long formulas into easily understood segments.

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.

Download the sample 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.

8. Highlight Matching Parenthesis in Nested IF Statements to See Mismatches

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.

9. Use the New IFS Function to “Nest” Up to 127 Levels of IF Functions

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.

IFS([logic_if_TRUE,result_if_TRUE,[logic2_if_TRUE,result2_if_TRUE],…[logic127_if_TRUE,result127_if_TRUE])

Click here to learn more about the IFS function at Microsoft’s Office support site.

10. Use IF to Test Cell Contents for Blank, Text, Number

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

ISBLANK

Cell is not blank

NOT(ISBLANK)

Cell contains text

ISTEXT

Cell does not contain text

NOT(ISTEXT)

Cell contains numbers

ISNUMBER

Cell does not contain numbers

NOT(ISNUMBER)

11. Control Errors with IFERROR and IFERR

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(ISERROR(formula),error_result,formula)

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,

=IFERROR(formula,error_result)

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.

12. Use Static or Dynamic Criteria to Calculate SUMIF, COUNTIF, or AVERAGEIF

IF functions can use static criteria and results or can use cell references.

Download the sample Excel tutorial worksheet.

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.

13. Use SUMIFS, COUNTIFS, or AVERAGEIFS to Sum, Count, or Average Values Matching Multiple Criteria

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.

14. Use SUMIFS, COUNTIFS, or AVERAGEIFS with a Date Range

SUMIFS allows multiple criteria. Here it is used to total across a date range.

SUMIFS allows multiple criteria. Here it is used to total across a date range.

Download the sample Excel tutorial worksheet.

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.

15. Use Dynamic Range Names instead of Cell References with SUMIF, SUMIFS, COUNTIF, COUNTIFS

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.

16. Use CHOOSE for Some Multiple Result Decisions

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.

17. Use VLOOKUP for One to One Lookup Decisions from a List

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.

18. Use F9 to See Partial TRUE/FALSE Conditional Results

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.

19. Use Evaluate Formula to See the Logical Decision Process

Use Evaluate Formula to watch each segment of a formula calculate.

Use Evaluate Formula to watch each segment of a formula calculate.

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.

 

Share the power...

Leave a Comment:

Leave a Comment: