How to Nest Multiple IF Functions in Excel

01
of 06

How Nested IF Functions Works

excel-2013-nested-if-fuction-1.jpg
Nesting IF Functions in Excel. © Ted French

The usefulness of the IF function can be extended by inserting or nesting multiple IF functions inside each other.

Nested IF functions increase the number of possible conditions that can be tested for and increase the number of actions that can be taken to deal with these outcomes.

The most recent versions of Excel allow 64 IF functions to be nested inside one another, while Excel 2003 and earlier permitted only seven.

Nesting IF Functions Tutorial

As shown in the image above, this tutorial uses just two IF functions to create the following 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=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, checks to see if an employee's salary is less than $30,000
  2. If it is, the middle part, $D$3*D7, multiplies the salary by the deduction rate of 6%
  3. If it is not, the second IF function: IF(D7>=50000,$D$5*D7,$D$4*D7)  tests two further conditions:
    • D7>=50000, checks to see if an employee's salary is greater than or equal to $50,000
    • If it is, $D$5*D7 multiplies the salary by the deduction rate of 10%
    • If is not, $D$4*D7 multiplies the salary by the deduction rate of 8%

Entering the Tutorial Data

Enter the data into cells C1 to E6 of an Excel worksheet as seen in the image above.

The only data not entered at this point is the IF function itself located in cell E7.

For those who do not feel like typing, the data and instructions for copying it into Excel are available at this link.

Note: The instructions for copying the data do not include formatting steps for the worksheet.

This will not 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

Starting the Nested IF Function

Adding the Arguments to the Excel IF Function
Adding the Arguments to the Excel IF Function. © Ted French

Although it is possible to just enter the complete formula

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

into cell E7 of the worksheet and have it work, 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.

For this example, the nested IF function will be 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 will be first entered into one cell E7 using absolute cell references for the deduction rates and then copied it to cells E8:E11.

Tutorial Steps

  1. Click on cell E7 to make it the active cell. - the location for the nested IF formula.
  2. Click on the Formulas tab of the ribbon
  3. Click on the Logical icon open the function drop down list.
  4. Click on 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 testing and what actions to take if the condition is true or false.

Tutorial Shortcut Option

To continue with this example, you can

  • 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 five pages which give detailed instructions and explanations for entering the three arguments.

03
of 06

Entering the Logical_test Argument

excel-2013-nested-if-fuction-3.jpg
Adding the Logic Test Argument to the Excel IF Function. © Ted French

The Logical_test argument is always a comparison between 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 will be 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. Click on the Logical_test line in the dialog box
  2. Click on 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 should read: D7 < 30000

Note: Do not enter the dollar sign ( $ ) or a comma separator ( , ) with the 30000.

An Invalid error message will appear at the end of the Logical_test line if either of these symbols is entered along with the data.

04
of 06

Entering the Value_if_true Argument

excel-2013-nested-if-fuction-4.jpg
Adding the Value If True Argument to the Excel IF Function. © Ted French

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% 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, however, having cell references change when a function is copied will result 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. Click on the Value_if_true line in the dialog box
  2. Click on cell D3 in the worksheet to add this cell reference to the Value_if_true line
  3. Press the F4 key on the keyboard to make D3 an absolute cell reference ( $D$3 )
  4. Press the asterisk ( * ) key on the keyboard - the asterisk is the multiplication symbol in Excel
  5. Click on cell D7 to add this cell reference to the Value_if_true line
  6. The completed Value_if_true line should read: $D$3 * D7

Note: D7 is not entered as an absolute cell reference because 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

Entering the Nested IF Function as the Value_if_false argument

Adding the Nested IF Function as the Value If False Argument
Adding the Nested IF Function as the Value If False Argument. © Ted French

Normally, the Value_if_false argument tells the IF function what to do when the Logical_test is false, but 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) test 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. Click on 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. Click OK to complete the IF function and close the dialog box
  4. The value of $3,678.96 should appear in cell E7*
  5. When you click on cell E7, the complete function
    =IF(D7=50000,$D$5*D7,$D$4*D7))
    appears in the formula bar above the worksheet​

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

If all steps have been followed, your example should currently match the very 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

Copying the Nested IF Functions using the Fill Handle

Copying the Nested IF Formula with the Fill Handle in Excel
Copying the Nested IF Formula with the Fill Handle. © Ted French

To complete the worksheet, the formula containing the nested IF function needs to be copied to cells E8 to E11.

As the function is copied, Excel will update 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. Click on cell E7 to make it the active cell.
  2. Place the mouse pointer over the black square in the bottom right corner of the active cell. The pointer will change to a plus sign " + ".
  3. Click the left mouse button and drag the fill handle down to cell E11.
  4. Release the mouse button. Cells E8 to E11 will be filled with the results of the formula as shown in the image above.