Joanna Zietara's Profile

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 29,701 or 71,950 in the above example. This is because the IF function uses the comma to separate the three sections 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.