avatar

The Excel IF() function - Yes, No or Maybe?

by Simon Hurst on 19.05.2010 07:22

A question was recently posted to the IT Counts forum about an IF() function that wasn’t working as expected. The likely reason seemed to be that the maximum number of levels of ‘nested’ (one function used as the argument for another function) functions had been reached. In Excel 2003 the maximum is 7.

Although the maximum has been increased marginally to 64 in Excel 2007, I’m not sure that the best answer is a version upgrade. It doesn’t take too many levels of nesting before formulae get very difficult to follow and become increasingly error-prone to work with.

Often, there are better alternatives to many nested levels of IF() functions. Let’s take the relatively straightforward example of a marking scheme where we want to apply a comment based on a score. I often think this is how my old school reports might have been created if schools had had computers in those days. In fact, I still bear the scars of one report which was generally good, but ruined by a D- for swimming, hence the following example.

In cell B1 we’ll enter the mark for swimming lessons and then create an appropriate comment:

A – Swims like a fish

B – Floats

C – Sinks a lot

We could do this by nesting IF() functions:

=IF(B1="A","Swims like a fish",IF(B1="B","Floats",IF(B1="C","Sinks a lot","No mark")))

The third ‘value if false’ argument of the first IF() contains another IF() function to check the second possible value, and in turn the ‘value if false’ argument of this IF() function contains a third IF() that checks for our third possible value.

One simple alternative to nesting IF() functions where the answers are mutually exclusive, is just to add them together (if the results are numbers) or concatenate (adding together for text) them if they are text strings. Given that our formula should only ever return one option, we’ll just be adding nothings to our correct result:

=IF(B1="A","Swims like a fish",) & IF(B1="B","Floats",) & IF(B1="C","Sinks a lot",)

Note that the ‘value if false’ argument for each IF() is set as nothing and the & operator is used to join the text strings together. Obviously, this only works if the options are exclusive. If more than one could evaluate as true, all the true values would either be added for numbers, or joined for text.

Perhaps a better alternative is to use VLOOKUP() and a table:

=VLOOKUP(B1,Report,2,FALSE)

VLOOKUP() compares the value in cell B1 with the first column of our table of marks and comments (Report) and – because we have set the optional fourth argument to FALSE – looks for an exact match. It then returns the result from that row and the column of our table entered as the third argument, in our case 2.     

This approach also makes it easier to cope with OR situations as you can just add additional rows to the table:

There’s more on VLOOKUP() on IT Counts and lookup functions and alternatives to IF() are covered on some of the Roadshow courses. The roadshow is about half way through now, but there are still a few places available for the remaining courses in Scotland, the South West and the grand finale in Southwold from late May to mid June.