How to Nest Multiple IF Functions in Excel

How to Nest Multiple IF Functions in Excel

rawpixel \ Unsplash

The usefulness of the IF function is extended by inserting, or nesting, multiple IF functions inside each other. Nested IF functions increase the number of possible conditions that are tested for and increase the number of actions that are taken to deal with these outcomes.

Note: 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.

01
of 06

Nest IF Functions Tutorial

Nesting IF Functions in Excel

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

=IF(D7<30000,$D$3*D7,IF(D7>=50000,$D$5*D7,$D$4*D7))

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

  1. The first part, D7<30000, checks to see if an employee's salary is less than $30,000.
  2. If the salary is less than $30,000, the middle part, $D$3*D7, multiplies the salary by the deduction rate of 6%.
  3. If the salary is greater than $30,000, the second IF function IF(D7>=50000,$D$5*D7,$D$4*D7) tests two further conditions.
  4. D7>=50000 checks to see if an employee's salary is greater than or equal to $50,000.
  5. If the salary is equal to or greater than $50,000, $D$5*D7 multiplies the salary by the deduction rate of 10%.
  6. If ​the salary is less than $50,000 but greater than $30,000, $D$4*D7 multiplies the salary by the deduction rate of 8%.

Enter the Tutorial Data

Enter the data into cells C1 to E6 of an Excel worksheet as seen in the image. The only data not entered at this point is the IF function itself located in cell E7.

Note: The instructions for copying the data do not include formatting steps for the worksheet. This doesn't interfere with completing the tutorial. Your worksheet may look different than the example shown, but the IF function will give you the same results.

 

02
of 06

Start the Nested IF Function

Adding the Arguments to the Excel IF Function

It is possible to just enter the complete formula

=IF(D7<30000,$D$3*D7,IF(D7>=50000,$D$5*D7,$D$4*D7))

into cell E7 of the worksheet and have it work. In Excel Online, this is the method you must use. However, if you are using a desktop version of Excel, it is often easier to use the function's dialog box to enter the necessary arguments.

Using the dialog box is a bit trickier when entering nested functions because the nested function must be typed in. A second dialog box cannot be opened to enter the second set of arguments.

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

Tutorial Steps

  1. Select cell E7 to make it the active cell. This is where the nested IF formula will be located.
  2. Select Formulas.
  3. Select Logical to open the function drop-down list.
  4. Select IF in the list to bring up the function's dialog box.

The data entered into the blank lines in the dialog box form the arguments of the IF function. These arguments tell the function the condition being tested and what actions to take if the condition is true or false.

Tutorial Shortcut Option

To continue with this example, you may:

  • Enter the arguments into the dialog box as shown in the image above and then jump to the final step that covers copying the formula to rows 7 through 10.
  • Or, follow through the next steps that offer detailed instructions and explanations for entering the three arguments.
03
of 06

Enter the Logical_test argument

excel-2013-nested-if-fuction-3.jpg

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

In this example, there are three salary levels that determine an employee's annual deduction:

  • Less than $30,000.
  • Between $30,000 and $49,999.
  • $50,000 or more

A single IF function can compare two levels, but the third salary level requires the use of the second nested IF function. The first comparison is between the employee's annual salary, located in cell D, with the 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 the values.

Tutorial Steps

  1. Select the Logical_test line in the dialog box.
  2. Select cell D7 to add this cell reference to the Logical_test line.
  3. Press the less than key ( < ) on the keyboard.
  4. Type 30000 after the less than symbol.
  5. The completed logical test displays as D7<30000.

Note: Do not enter the dollar sign ( $ ) or a comma separator ( , ) with the 30000. An invalid error message appears at the end of the Logical_test line if either of these symbols is entered along with the data.

04
of 06

Enter the Value_if_true Argument

Excel Nested IF Function Arguments

The Value_if_true argument tells the IF function what to do when the Logical_test is true. The Value_if_true argument can be a formula, a block of text, a value, a cell reference, or the 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 deduction rate of 6 percent located in cell D3.

Relative vs. Absolute Cell References

Normally, when a formula is copied to other cells, the relative cell references in the formula change to reflect the formula's new location. This makes it easy to use the same formula in multiple locations. Occasionally, having cell references change when a function is copied results in errors. To prevent these errors, the cell references can be made Absolute, which stops them from changing when they are copied.

Absolute cell references are created by adding dollar signs around a regular cell reference, such as $D$3. Adding the dollar signs is easily done by pressing the F4 key on the keyboard after the cell reference has been entered into the dialog box.

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

Tutorial Steps

  1. Select the Value_if_true line in the dialog box.
  2. Select cell D3 in the worksheet to add this cell reference to the Value_if_true line.
  3. Press the F4 key to make D3 an absolute cell reference ($D$3).
  4. Press the asterisk ( * ) key. The asterisk is the multiplication symbol in Excel.
  5. Select cell D7 to add this cell reference to the Value_if_true line.
  6. The completed Value_if_true line displays as $D$3*D7.

Note: D7 is not entered as an absolute cell reference. It needs to change when the formula is copied to cells E8:E11 in order to get the correct deduction amount for each employee.

05
of 06

Enter the Nested IF Function as the Value_if_false Argument

Adding the Nested IF Function as the Value If False Argument

Normally, the Value_if_false argument tells the IF function what to do when the Logical_test is false. In this case, the nested IF function is entered as this argument. By doing so, the following results occur:

  • The Logical_test argument in the nested IF function (D7>=50000) tests all salaries that are not less than $30,000.
  • For those salaries greater than or equal to $50,000, the Value_if_true argument multiplies them by the deduction rate of 10% located in cell D5.
  • For the remaining salaries (those that are greater than $30,000 but less than $50,000) the Value_if_false argument multiplies them by the deduction rate of 8% located in cell D4.

Tutorial Steps

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

Note: Nested functions do not start with an equal sign, but rather with the function's name.

  1. Select the Value_if_false line in the dialog box.
  2. Enter the following IF function:
    IF(D7>=50000,$D$5*D7,$D$4*D7)
  3. Select OK to complete the IF function and close the dialog box.
  4. The value of $3,678.96 appears in cell E7. Since R. Holt earns more than $30,000 but less than $50,000 per year, the formula $45,987 * 8% is used to calculate his annual deduction.
  5. Select cell E7 to display the complete function =IF(D7=50000,$D$5*D7,$D$4*D7)) in the formula bar above the worksheet.

After following these steps, your example now matches the first image in this article.

The last step involves copying the IF formula to cells E8 to E11 using the ​fill handle to complete the worksheet.

06
of 06

Copy the Nested IF Functions Using the Fill Handle

Copying the Nested IF Formula with the Fill Handle in Excel

To complete the worksheet, copy the formula containing the nested IF function to cells E8 to E11. As the function is copied, Excel updates the relative cell references to reflect the function's new location while keeping the absolute cell reference the same.

One easy way to copy formulas in Excel is with the Fill Handle.

Tutorial Steps

  1. Select cell E7 to make it the active cell.
  2. Place the mouse pointer over the square in the bottom right corner of the active cell. The pointer will change to a plus sign (+).
  3. Select and drag the fill handle down to cell E11.
  4. Cells E8 to E11 are filled with the results of the formula as shown in the image above.