Truth be told, a nested IF solution can always be replaced by the far superior LOOKUP functions.

This little gem finds its way into Matric finals over and over again. Getting your head around the logic takes some practice, so let’s begin! It is also one of the only ways to fulfil the Level 4 requirement of the Grade 12 Excel analysis.

##### In this post:

##### Required knowledge:

## 1. General

- If something is true/a condition is met,
- do this,
- otherwise, do that.

If our brains could not process data in this fashion, we would die, or at the very least, be incredibly miserable!

- If you are hungry, eat, if not, don’t eat
- If you want to get demerits at school, don’t do your homework, if you don’t, do your homework

## 2. Assessment example

Let us say it is sports day at school and we need to award gold, silver, and bronze medals to the first three athletes in an event. Following the above logic for the IF statement:

- If the athlete placed first
- award them the gold medal
- otherwise, award them the silver medal

Oh dear: that won’t work — no bronze medal! Let’s try again:

- If the athlete is in first place
- award them the gold medal
- if the athlete placed second
- award them the silver medal
- otherwise, award them a bronze medal.

Here we have an IF statement with an IF statement “inside” as the FALSE part of the first, main IF statement. Now we need Excel to perform this logic…

The formula in cell C2 (which can then be copied downwards) is:

`=IF(B2=1,"GOLD",IF(B2=2,"SILVER","BRONZE"))`

The following would also work:

`=IF(B2<2,"GOLD",IF(B2<3,"SILVER","BRONZE"))`

In the second example, 1 is less than 2 AND 1 is less than 3, so how does Excel decide whether to award GOLD or SILVER?

Excel evaluates the conditions from left to right and stops (*exits*) as soon as the condition is met. So in the above example, the function returns GOLD and exits the function.

## 3. PAT example

Here, however, is one way to use a nested IF: group data into “categories” based on ranges of numeric data.

In the example below, respondents were asked how many times they ate ice cream per year. The answers are categorised as follows:

- If more than 12 times =
**More than once a month** - If 12 times a year =
**Once a month** - If 0 times per year =
**NEVER** - else they eat ice cream
**Less than once a month**

The resulting formula is:`=IF(F2>12,"More than once a month",IF(F2=12,"Once a month",IF(F2=0,"Never","Less than once a month")))`

## 4. Largest of 3 algorithm

In the Largest of 3 in algorithm tutorial, we created the following flowchart:

We will now create a Nested IF statement in Excel.

Start small: if we have 2 numbers, using an IF statement to determine the largest of the 2 is pretty simple:

`=IF(A2>B2,A2,B2)`

We can build on this to determine the largest of 3 numbers:

`=IF(A2>B2,IF(A2>C2,A2),IF(B2>C2,B2,C2))`