How to nest multiple IF functions in Excel

The utility of an IF function is extended by insertion or nesting multiple IF functions into each other. Nested IF functions increase the number of possible conditions that are tested and increase the number of actions that are taken to resolve those problems.

Remark † The instructions in this article apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, and Excel Online.

Content
  1. Nest IF Feature Guide
  2. Enter training data
  3. Run a nested IF function
  4. self study
  5. Tutorial Label Option
  6. Enter the Logical_test argument
  7. self study
  8. Enter the Value_if_true argument
  9. Relative and Absolute Cell References
  10. self study
  11. Enter nested IF function as argument Value_if_false
  12. self study
  13. Copy nested IF functions with fill handle
  14. self study

Nest IF Feature Guide

As shown in the figure, this tutorial uses two IF functions to create a formula that calculates the annual deduction for employees based on their annual salary. The formula used in the example is shown below. The nested IF function acts as the value_if_false argument to the first IF function.

 = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7)) 

The different parts of the formula are separated by commas and perform the following tasks:

  1. First part, D7

  2. If the salary is less than $30,000, multiply the middle part, $D$3 * D7, by the 6% deduction percentage.

  3. If the salary is greater than $30,000, the second function IF IF(D7 >= 50000, $D$5*D7, $D$4*D7) checks two additional conditions.

  4. D7 >= 50,000 checks if the employee’s salary is greater than or equal to $50,000.

  5. If the salary is equal to or greater than $50,000, then $5 * $5 multiplies the salary by the 10% deduction.

  6. If the salary is less than $50,000 but more than $30,000, D$4 * D7 multiplies the salary by the 8% deduction percentage.

Enter training data

Enter data in cells C1 to E6 of the Excel worksheet as shown in the figure. The only data that hasn’t been entered at this point is the IF function itself, which is located in cell E7.

Remark † The instructions for copying data do not include steps for formatting the worksheet. It does not hinder the completion of the lesson. Your worksheet may differ from the example worksheet, but the IF function will give you the same results.

Run a nested IF function

You can just enter the full formula

 = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7)) 

to cell E7 of the worksheet and let it work. In Excel Online, this is the method to use. However, if you’re using the desktop version of Excel, it’s often easier to use the function’s dialog box to enter the required arguments.

Using the dialog box is a bit trickier when entering nested functions, because you have to enter a nested function. Cannot open a second dialog for entering a second set of arguments.

In this example, the nested IF function is entered on the third line of the dialog box as the Value_if_false argument. Because the worksheet calculates annual retention for multiple employees, the formula is first entered into cell E7 using absolute cell references for retention rates and then copied to cells E8:E11.

self study

  1. Select cell E7 to make it active. This is where the nested IF formula resides.

  2. Select formulas

  3. Select Logical to open the drop-down list of functions.

  4. Select IF in the list to open the function’s dialog box.

The data entered on the empty lines in the dialog are the arguments for the IF function. These arguments tell the function what condition to test and what action to take if the condition is true or false.

Tutorial Label Option

To continue with this example, you can:

  • Enter the arguments in the dialog box, as shown in the image above, and then proceed to the last step, which involves copying the formula on lines 7 to 10.

  • Or follow the steps below, which provide detailed instructions and explanations for entering three arguments.

Enter the Logical_test argument

The Logical_test argument compares two data items. This data can be numbers, cell references, formula results, or even text data. To compare two values, Logical_test uses a comparison operator between values.

In this example, there are three pay levels that determine an employee’s annual retention:

  • Less than $30,000.

  • From $30,000 to $49,999.

  • $50,000 or more

A single IF function can compare two levels, but the third salary level requires a second nested IF function. The first comparison is between the employee’s annual salary, which is in cell D, with a threshold salary of $30,000. Since the goal is to determine if D7 is less than $30,000, the Less Than operator is used between values ​​(

self study

  1. Select line Logic test in the dialog box.

  2. Select cell D7 to add this cell reference to the string Logical_test.

  3. Press the less key ( ) on keyboard.

  4. Enter 30000 after the less than symbol.

  5. A completed logic test is displayed as D7

Remark † Do not enter a dollar sign () or comma separator () with 30000. An invalid error message is displayed at the end of the Logical_test string if any of these characters are entered along with data.

Enter the Value_if_true argument

The Value_if_true argument tells the IF function what to do if Logical_test is true. The Value_if_true argument can be a formula, a block of text, a value, a cell reference, or a cell can be left blank.

In this example, when the data in cell D7 is less than $30,000, Excel multiplies the employee’s annual salary in cell D7 by the 6 percent deduction percentage in cell D3.

Relative and Absolute Cell References

When a formula is copied to other cells, the relative cell references in the formula usually change to reflect the formula’s new location. This makes it easy to use the same formula in multiple places. Sometimes cell references change when a function is copied, resulting in errors. To avoid these errors, make cell references absolute, which prevents them from being changed when copied.

Absolute cell references are created by adding dollar signs around a normal cell reference, such as $D$3. Adding dollar signs is easy by pressing the key F4 on the keyboard after entering the cell reference in the dialog box.

In this example, the retention rate in cell D3 is entered as an absolute cell reference in the Value_if_true line of the dialog box.

self study

  1. Select line value_if_true in the dialog box.

  2. Select cell D3 on the sheet to add this cell reference to the Value_if_true string.

  3. Press key F4 to make D3 an absolute cell reference ($D$3).

  4. Press the star key († The asterisk is the multiplication symbol in Excel.

  5. Select cell D7 to add this cell reference to the Value_if_true string.

  6. The entered string Value_if_true is displayed as $D$3*D7.

Remark :D7 is not specified as an absolute cell reference. It must be changed when the formula is copied to cells E8:E11 to get the correct deduction amount for each employee.

Enter nested IF function as argument Value_if_false

Typically, the Value_if_false argument tells the IF function what to do if Logical_test is false. In this case, the nested IF function is injected as this argument. This produces the following results:

  • The Logical_test argument in the nested IF function (D7 >= 50000) tests all salaries that are at least $30,000.

  • For those salaries greater than or equal to $50,000, the Value_if_true argument multiplies them by the 10% deduction percentage in cell D5.

  • For the remaining salaries (which are greater than $30,000 but less than $50,000), the Value_if_false argument multiplies them by the 8% subtraction percentage in cell D4.

self study

As mentioned at the beginning of the tutorial, the second dialog cannot be opened to enter a nested function, so it must be entered in the Value_if_false line.

Remark † Nested functions do not start with an equal sign, but with the name of the function.

  1. Select line value_if_false in the dialog box.

  2. Enter the following IF function:

     IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) 
  3. Select Okay to end the IF function and close the dialog box.

  4. The value $3678.96 appears in cell E7. Since R. Holt earns over $30,000 but less than $50,000 per year, the formula used to calculate his annual deduction is 45,987 * 8%.

  5. Select cell E7 to display the full function =IF(D7=50000, $D$5*D7, $D$4*D7)) in the formula bar above the worksheet.

After completing these steps, your example will now match the first image in this article.

The last step involves copying the IF formula into cells E8-E11 using the fill handle to fill the table.

Copy nested IF functions with fill handle

To complete the worksheet, copy the formula containing the nested IF function into cells E8-E11. When a function is copied, Excel updates the relative cell references to reflect the new location of the function, while preserving the absolute cell reference.

An easy way to copy formulas in Excel is with the fill handle.

self study

  1. Select cell E7 to make it active.

  2. Hover over the square in the lower-right corner of the active cell. The pointer changes to a plus sign (+).

  3. Select and drag the fill handle to cell E11.

  4. Cells E8-E11 are filled with formula results, as shown in the image above.

Leave a Reply

Your email address will not be published.