ABC ~ All 'Bout Computers
The Online Web-azine for Computer Enthusiasts
-- brought to you by

contents page for this issue

No More Nested IFs
~~Ray Blake, GR Business Process Solutions

Techniques with Excel to simplify the nested IF formula structure

Don't get me wrong; I'm a big fan of the IF function, and I regularly use nested Ifs to do things it would be difficult to do in any other way. But they tend to come with a price tag. Two weeks after you build a nested IF, you will find it extremely difficult to debug. Why? Because the tortured logic of the nested structure isn't one with which the human brain has been equipped to deal by the main evolutionary line of progression.

Have a look at this one, for instance:

=IF(MONTH(NOW())=12,IF(DAY(NOW())<=25,"Happy Christmas","Happy New Year"),IF(MONTH(NOW())=2,IF(DAY(NOW())<=14,"Happy Valentine's Day",""),""))

This formula says, "Happy Christmas" if today falls in December, but only up until 25 December. Thereafter, for the remainder of that month, it will return "Happy New Year". From 1 February to 14 February, it will return "Happy Valentine’s Day". In all other cases, it returns a null string. Trying to count all those nested parentheses, commas and quotes is tricky to say the least. Point made.

What I would like to do in this article is to outline some alternative approaches, which might help us restrict the use of the nested IF formula to those events where it is absolutely necessary, given the pain involved in debugging and even understanding these structures!

The first technique is to use Excel’s logical functions. Consider, for instance, the following set of data:

We have here some soccer players each of whom wears a distinctive playing kit, comprising boldly coloured shirts, shorts and socks. Note that in column E, I am trying to determine which of them has a kit which includes green. The formula in cell E2, for instance, reads:

=IF(B2="Green","Yes",IF(C2="Green","Yes",IF(D2="Green","Yes","No")))

You might want to follow the logic inherent in the nesting order to see how this works, or you may feel, like me, that life is short enough already.

In column F, however, I need a different structure, because here I want to determine which players have an all-green kit. The formula in F2 reads:

=IF(B2="Green",IF(C2="Green",IF(D2="Green","Yes","No"),"No"),"No")

(I just love that "No, No, No" at the end, by the way; it seems to sum up how I feel about the whole issue!)

So, two pretty impenetrable constructions. Each of them can be replaced using Excel’s logical functions to offer far greater clarity. Let’s first look at the E2, part-green formula:

=IF(B2="Green","Yes",IF(C2="Green","Yes",IF(D2="Green","Yes","No")))

My preferred version of this is:

=OR(B2="Green",C2="Green",D2="Green")

Isn’t that inherently better, aesthetically, or am I just a sad geek? (That’s a rhetorical question, by the way – please don’t email an opinion on this.)

Do you see how the word ‘OR’ immediately lets you know what kind of checking the formula is about, instead of all the comma counting you need for the IF version?

Now let’s turn to the F2, all-green formula:

=IF(B2="Green",IF(C2="Green",IF(D2="Green","Yes","No"),"No"),"No")

Here’s my replacement:

I can almost hear your sighs of contentment. You’re right; this is a far more immediately satisfying formula. Again, the first word – "AND" – signals straight away the kind of relationship we’re looking for in the data.

Incidentally, the logical functions will return as answers either ‘TRUE’ or ‘FALSE’, rather than the "Yes" or "No" we had our IF functions returning. Is that a big problem? If it is, you can just wrap the logical function in an IF to convert its output, like this:

=IF(AND(B2="Green",C2="Green",D2="Green"),"Yes","No")

Personally, I prefer the ‘TRUE’ and ‘FALSE’, but I realise that it takes all sorts.

If you work with nested IFs for very long, there is every chance that you will become frustrated when you cannot nest them deeper than 6 levels. Consider the sheet shown overleaf, for instance:

The formula behind B1 is:

=MONTH(A1)

Now, instead of simply a number, I want it to tell me the name of the month. One way to do this, of course, is to do what I've done in cell A3. Here I've put the same date as appears in A1, but I've given the cell the special format "MMMM" which will show the full month name and suppress all other date information.

This works until I want to use that month name in another cell as part of a formula. For instance, cell C3 is supposed to read, "A July wedding!" Its formula is:

="A "&A3&" wedding!"

The cell formatting, of course, doesn't change the data in the cell, just the way it appears, so instead of "A July wedding!" I seem to be saddled with, "A 38192 wedding!" How lovely.

My next approach is more successful, and it hinges around the formula in cell B5. Here, we take the month number of the date, just as we did in B1, and turn it into the month name. If it's 1, we want "January", if it's 2, we want "February", and so on. A nice nested IF would do the trick, along the lines of:

=IF(B1=1,"January",IF(B1=2,"February",IF(B1=3,"March"…

You can see what's coming, can't you? We actually need 11 levels of nesting to make this one work for all 12 months, and IF will allow only 6 levels. Now, you might argue that the wedding season could probably be accommodated since few people get married in the autumn or winter months, but this won't really do!

Another approach would be to have a little LOOKUP table we could use, but this will consume at least 24 other cells. There is a function which will allow us to perform the necessary conversion fully within the confines of a single cell. Here's what I have in B5:

=CHOOSE(MONTH(A5), "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

Another non-IF triumph, I'm sure you'll agree! In fact, the CHOOSE function is so elegant and useful it deserves an article all to itself and I've a mind to tackle the task myself one day soon.

Let's end the article where we started it, with that nice seasonally-adjusted greeting:

=IF(MONTH(NOW())=12,IF(DAY(NOW())<=25,"Happy Christmas","Happy New Year"),IF(MONTH(NOW())=2,IF(DAY(NOW())<=14,"Happy Valentine's Day",""),""))

Now, the techniques I've outlined in this article don't really help us recode this one and to be perfectly honest, this is one of those occasions where I'd probably go with the nested IF, or possibly set up a lookup table. But it strikes me that some readers might enjoy the challenge of trying to create an alternative approach. The rules are simple: create a formula which does the same thing, is entirely contained in the one cell and avoids nested IFs. Send your formulae to ray@grbps.com and I'll include any successes in an update to this article in due course.

Ray Blake lives in England and spent 15 years training people in the financial services industry there. He had always used PCs in his work, and gradually realized he might make a career out of them. He and his business partner set up GR Business Process Solutions (www.grbps.com) which specializes in innovative IT to support knowledge testing and skills assessment. Although he spends a lot of time these days developing in VB and Access, Excel remains his favorite development tool, because, as he says, 'It can do everything; there's no computer application you can think of that you couldn't develop in Excel.'