Excel Function: IF (nested)
The Excel IF function can be nested (like other Excel functions), allowing the addition of new tests and results.
In this example, a third rate is added in cell B11 which is renamed "retired" (as was the case for the first 2 cells on the previous page):
It is therefore now necessary to add a third rate to the IF function.
The beginning is the same as before, the IF function will test if the age is less than 16 and if so, it will display the child rate.
Now click in the Value_if_false field and then on the "IF" in the list at the top left to add a new condition:
A new IF function has now been added in the Value_if_false field (so if the first test is false, a second test will be performed).
The second test will have to check if the age is equal to or greater than 60. If so, the function will display the retired rate, and if not, it will display the adult rate:
=IF(B2<16,child,IF(B2>=60,retired,adult))
Then stretch the formula down to get:
If you subsequently modify the rates (adult, child, retired) or ages, the results will be automatically recalculated.