How to use nested IF statments in Microsoft Excel

IF function is one of the most useful functions available in Excel. By introducing decision making to a spreadsheet, the IF function test if a certain condition is true or false. It is possible to Nest or place one IF function inside another, so we can increase the spreadsheets' flexibility.

The basic form of the IF function is:

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

Nesting IF functions increases the number of possible outcomes.

For example, let's work on an Hypothetical Savings Plan Deduction. 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 the income is:

• less than $16,400, the deduction rate is 12%
  
  
• greater than or equal to $16,400, but less than $68,350, the deduction rate is 17%
  
  
• greater than or equal to $68,350, the deduction rate is 25%

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<16,400,A5*15%)

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

=IF(A5<16,400,a5*12%,if(a5<68,350,a5*17%,a5*25%))

The logic test of the Nested IF function, checks to see if the income is greater than or equal to $16,400, but less than $68,350. If it is, the deduction rate is 17%. If the income is greater than or equal to $71,950, the deduction rate is 25%. Additional rate changes could be added another nested IF functions inside the existing function.

Just remember that limit of seven nested IFs though, because there is no way to increase this using an IF statement. However, when you really need more than eight conditions, there are other functions that will serve you better than Nested IFs. One set of functions are the LOOKUP functions.