Excel Training: IF Function and Nesting

Referencing cells

Instead of displaying "Yes" or "No" based on age as was the case on the previous page, this time we will return the value of a cell.


The IF function will now have to display the price according to age:

excel function price nested if functions

Insert the IF function and then enter the same logical test as on the previous page B2>=18 (which checks if the age is greater than or equal to 18).

The value if TRUE must be here the reference to the cell containing the adult fare and the value if FALSE, the reference to the child fare:

excel if function price nested functions

Remember that references are offset when copying, so you still need to add the $ to the fare references:

excel if function price dollar nested functions

The formula =IF(B2>=18,$F$3,$F$2) is now ready to be copied:

excel function prices copy nested if functions

The price of each is then displayed:

excel function prices nested if functions

Nesting a function within another

To achieve the desired result, it will sometimes be necessary to nest several functions.

Let's add an additional fare for this example:

excel function 3 prices nested if functions

In this case, if the person is under 18 years old, the fare does not change. However, for an adult 2 fares remain possible (adult or retired), so a second logical test needs to be added.

To open the function window, select cell C2 and click on "Insert a function" from the "Formulas" tab:

excel if function window nested functions

Then delete the content of the value if TRUE (since it will depend on the second test):

excel if function nested functions

And add a new IF function by selecting it from the list on the left:

excel if function nests nested functions

Then enter the second logical test B2>=65 and the corresponding fares (don't forget the $):

excel if functions nested

The formula =IF(B2>=18,IF(B2>=65,$F$4,$F$3),$F$2) contains 2 IF functions and now allows to display the prices according to 3 age brackets:

excel if function nesting nested functions

The result after copying:

excel if function 3 prices nested functions