Excel Tip: Nested IF Statements - Decisions dependent on other decisions

In last week's post I discussed AND and OR, as ways of combining criteria in an IF statement. These are perfect if each test that you wish to apply is independent of the others.

What about, if you wish to apply additional tests based upon the results of other tests, resulting in more than to possible outcomes?

In real life this might be something like:

"If it is sunny today, then if all of the family agree we will go for a walk, but if they don't, we will go for a picnic - however, if it is not sunny, then if there is a football match on tv, we will watch that, otherwise we will watch a film."

This kind of thing is easier than you might think, if you keep your head!

If you need a refresher on the IF statement before we start then visit my earlier post:

EXCEL TIP: The IF Statement made simple

The answer to this kind of problem is the use of something called Nested IF statements. In Excel, we can 'nest' functions inside other functions. This means using a function as an argument inside another function. To address the kind of problem above, we can nest IF functions inside of other IF functions to produce the logic that we want.

These can lead to pretty complicated-looking formulae when you are finished, but need not be complicated to build if you take them step-by-step.

For our example let us say that we define the following cells as range names:

A1 as sunny
A2 as familyagreewalk
A3 footballontv

Each cell can contain either Y or N, for Yes or No.

We could just use the cell references but it will be easier to see the logic of the formula with the defined names.

I sometimes find it useful to do one IF statement at a time, placing dummy answers where I will later place a further if function. Taking this approach we can apply the first text as follows:

=IF(sunny="Y","AAA","BBB")

This will simply return AAA if it is sunny, or BBB if it is not. We can then replace "AAA" with the test that we wish to apply if it is sunny, which is IF(familyagreewalk="Y","Walk","Picnic"), this makes our formula:

=IF(sunny="Y",IF(familyagreewalk="Y","Walk","Picnic"),"BBB")

We can the replace "BBB" with the test that we want to apply if it is not sunny, i.e. IF(footballontv="Y","Football","Film"), so our formula becomes:

=IF(sunny="Y",IF(familyagreewalk="Y","Walk","Picnic"),IF(footballontv="Y","Football","Film"))

We can keep adding nested IFs in this way. We may want to replace "Film" with a rule that determines what kind of film we will watch, for example.

Since Excel 2007, you can actually have up to 64 Nested IFs in one function, although your formula would get pretty insane well before that! Excel 2003 only allowed 7, which is already starting to get a little too complex. Usually when you have more than around 4 or 5, there is usually a better way of doing it. Lookups can be helpful, for example.

You can comfortable build up 4 or 5 if you use the approach above. Trying to go straight into writing the whole function, you can very quickly get your brackets in a knot!

Good Luck!







If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".