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:

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:

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

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

The price of each is then displayed:

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:

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:

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

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

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

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:

The result after copying: