1. Computing

Nested IF Functions in Excel Quick Tutorial

Using Multiple IF Functions in Excel

By

Related Tutorial: - Excel Nested IF Function Step by Step Tutorial

What the IF function does

The IF function is one of Excel’s most useful and most used functions. What it does, basically, is test to see whether a certain condition is true or false. If the condition is true, the function will do one thing, if the condition is false, the function will do something else.

The basic form or syntax of the function is:

=IF(logic test, value if true, value if false)

The logic test is always a comparison between two values. Comparison operators are used, for example, to see if the first value is greater than or less than the second, or equal to it. The "value if true" and "value if false" parts of the function are known as arguments. They tell the function what to do, depending on the outcome of the logic test.

Nested IF Functions

A Nested IF function is when a second IF function is placed inside the first in order to test additional conditions. "Nesting" IF functions increases the flexibility of the function by increasing the number of possible outcomes.

For example, deductions from an employee's income usually depends on employee income. The higher the income, the higher the deduction rate. We can use an IF function to determine what the deduction rate will be.

For this example, if employee income is:

  • less than $29,701, the deduction rate is 15%

  • greater than or equal to $29,701, but less than $71,950, the deduction rate is 25%

  • greater than or equal to $71,950, the deduction rate is 28%

The first deduction rate is handled by the logic test and the value if true argument of the first IF function. To do this, we write the beginning of the IF function as:

=IF(A5 < 29701, A5*15%,

To add the second and third deduction levels, we nest one IF function inside another. For example:

=IF(A5<29701,A5*15%,IF(A5<71950,A5*25%,A5*28%))

The logic test of the Nested IF function, checks to see if a employee’s income is greater than or equal to $29,701, but less than $71,950. If it is, the deduction rate is 25%. If the income is greater than or equal to $71,950, the deduction rate is 28%. Additional rate changes could be added another nested IF functions inside the existing function.

Note: there is no comma separator in the numbers 29,701 or 71,950 in the above example. This is because the IF function uses the comma to separate the three sections or arguments of the IF function contained within the round brackets.

If you use a comma as a separator in numbers greater than a thousand, Excel will give you an error message saying you have too many arguments in your function.

Related Tutorials on the IF Function:

Related Video
Cell References in Excel
Excel Lists- Remove Duplicates

©2014 About.com. All rights reserved.