Excel Function: IF (nested)

The Excel IF function can be nested (like other Excel functions), allowing the addition of new tests and results.

This tutorial is the continuation of the page: IF function

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):

nested ifs

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:

second if nested ifs

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).

excel if function nesting nested ifs

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))
ifs nested

Then stretch the formula down to get:

if formula nested ifs

If you subsequently modify the rates (adult, child, retired) or ages, the results will be automatically recalculated.

If needed, you can download the Excel file used here: nested-if.xlsx