Nested IFs. We have all done them but there comes a time to find a better way.
In Excel 2003 you were limited to 7 levels of nesting but 2007 onwards the level could be increased to 64. Both these levels are beyond my pain barrier for an overly complex formula fraught with danger. Even if you can get them right not only won’t other users understand them, I bet you won’t either if you come back to it 3 months later. Plus the formula might be so complex you haven’t thought of all the outcomes and the formula might work great for ages but one day the data the formula uses returns an unexpected TRUE or FALSE error (or worse incorrect result) and you only have 15 minutes left to get a report out. You will feel much more in control if you have a simpler formula you can readily understand.
A good place to start in avoiding nested IFs or simplifying nested IF’s is John Walkenbach’s post alternatives to nested IFs – I used to have link to this but it’s changed location but worth Googling. If you only have to return values from the decision process then a Lookup is the better way to go. Lookups are a whole topic in themselves but start with VLOOKUP if this is the case in hand – I have evolved to using INDEX/MATCH combination formulas but VLOOKUP is great so long as your lookup data is sorted and the return values to the right of the value being looked up. INDEX/MATCH avoids the need for sorted lookup data and you can index left or right of the lookup. But what if you don’t want to return just values but you need different formulas to apply depending on the situation?
CHOOSE is the first alternative I think of to a nested IF situation, if I want to return formulas (or formulas and values) and not just values only depending on the test result. Syntax is =CHOOSE(Index_number, value1, [value2],…) . In English, CHOOSE evaluates the Index_number part of the formula and the index number result tells it which value or formula to apply detailed in the second part of the formula. How simple is that? So if you have say 6 mutually exclusive situations and so up to 6 possible formulas or values to apply, CHOOSE is ideal to hold those 6 different formulas or values and allow you to do the one test to determine which formula kicks in. Note towards the bottom of John Walkenbach’s post the reference to using Boolean multiplication. So we can take using CHOOSE one step further and more powerfully by making the index in the formula a test of all mutually exclusive possibilities that still returns a 1,2…n index number because TRUE is treated as 1 and FALSE is treated as 0. So in a mutually exclusive situation only one index number can be return as all other parts of the test are multiplied by 0. If it has a an index number it can then apply the value or formula corresponding to that index number ie 1 then the first value/formula applies, if 4 then the 4th applies and so on.
On a consulting job the client had quite complex business rules where various overhead, recovery rates and margins applied to different expense types. So I found two situations really suited to using CHOOSE.
I wanted the same formula in each row no matter the expense type and each expense type needed a different formula. Basically the user picked an expense type from a validation dropdown in the entry area which returned an index number via lookup – I used Index/Match to do this but I could have also used VLOOKUP (another story as to why). The CHOOSE formula’s index number referred to this expense row index number and the formula corresponding to this index number applied. So this allowed a simple, consistent formula for all rows no matter the expense type to be copied down. So my formula looked like =CHOOSE(Cell containing Expense Type number i.e. 1…6 , Fx for Expense Type 1, Fx for Expense Type 2 and so on).
Elsewhere on this job I used the boolean variation of this formula John Walkenbach talks about where the index part of the formula looks like this:
(A1=”A”)*1+(A1=”B”)*2+(A1=”C”)*3+(A1=”D”)*4+(A1=”E”)*5
It works because which ever of the mutually exclusive conditions (which is essential to have for this to work) returns TRUE this will be seen as 1 and return the 1,2,3,4 or 5 index that tells CHOOSE what value/formula to apply, because all the other conditions will be FALSE and thereby multiplied by 0