Excel Nested If Functions

The Excel nested If is generally used to specify further conditions within an Excel If function. This is illustrated in the following examples.


Nested If Function with One Level of Nesting

Imagine you want to write a function to do the following calculations with the contents of cells A1 and B1:

This calculation can be performed by the following Excel nested if:

=IF( B1=0, "n/a", IF( A1<0, -A1/B1, A1/B1 ) )

In the above example, the nested If function is shown in red, inside the outer If function. It is seen that the nested If provides further conditions, to be applied if the condition of the outer If function (i.e. the condition B1=0) is FALSE.


Nested If Function with Two Levels of Nesting

Now imagine that if the value in cell A1 is greater than 100, you want to count this as the value 100. The conditions that we want to apply are now:

This can be done by introducing a further nested if function within the formula:

=IF( B1=0, "n/a", IF( A1<0, -A1/B1, IF( A1>100, 100/B1, A1/B1 ) ) )

In the above example, the first nested If is shown in red and the second level of nesting is shown in green.


Function Nesting Limitations

The above example shows 3 if functions, with 2 levels of nesting. You can take this further by repeatedly nesting functions within functions. However, there is a limit to the depth of nesting that Excel can handle. Excel 2003 can only handle 7 levels of nesting but Excel 2007 & Excel 2010 can both handle up to 64 levels of nesting.

Return to the Excel Formulas Page

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net