The Excel nested If is generally used to specify multiple conditions within an Excel If function. This is illustrated in the following examples.
Imagine you want to write a function to do the following calculations with the contents of cells A1 and B1:
Otherwise (i.e. if B1 is not equal to 0) then:
- if A1 < 0 return -A1/B1
- Otherwise return A1/B1
This calculation can be performed by the following Excel nested if:
In the above example, the nested If function is shown in red, inside the outer If function. It is seen that, in this case, the nested If provides further conditions, to be applied if the condition B1=0 is FALSE.
Now imagine that you want to add a further condition to the above nested if function, which tests if the value in cell A1 is greater than 100. If so, you want the value in A1 to be counted as the value 100. The conditions that we want to apply are now:
Otherwise (i.e. if B1 is not equal to 0) then:
- if A1 < 0 return -A1/B1
- Otherwise (i.e. if A1 ≥ 0) then:
if A1 > 100 return 100/B1
Otherwise return A1/B1
This can be done by introducing a further nested if function within the formula:
In the above example, the first nested 'If' is shown in red and the second level of nesting is shown in green.
The above example has 2 levels of nesting. You can take this further by repeatedly nesting functions within functions.
There is a limit to the depth of nesting that Excel can handle. Current versions of Excel (Excel 2007 and later) can handle 64 levels of nesting but Excel 2003 can only handle 7 levels of nesting.However, if you are applying this many nesting levels, you should consider whether a different function can be used to perform the required task more efficiently.