Excel alternatives to nested IF’s from our readers.
After David Goodmanson’s features on IF and nested IF’s we’ve received many messages from readers suggesting another alternative – CHOOSE() and here even more possiblities.
Tony D writes:
“
Small snippet for your “IF” library next time. I’m sure there will be hundreds of better ideas, but you can embed an array of choices in the formula.
=MATCH(“C”,{“A”;”B”;”C”},0)
returns 3, for the third position
or
=IF(ISERROR(MATCH(“D”,{“A”;”B”;”C”},0)), “FALSE”, “TRUE”)
would also work, giving False in this case
“
Paul H writes:
“
I enjoyed reading David Goodmanson’s article on Nested “Ifs and Alternatives” (OfMM#10.12), and hearterly endorse the use of LookUp Tables and VBA as an alternative.
It was surprising, however, that the AND(…) and OR(…) functions were not mentioned, as they can reduce a highly complex (and messy) nested function, as in David’s great example, to a function with only 2 levels of nesting.
In David’s example, the formula is testing to see if cell “A2” contains any of a series of letters, i.e. “A” or “B” or “C” etc. This could be written as:
=IF(OR(A2=”A”,A2=”B”,A2=”C”,…),TRUE,FALSE)
This provides a smaller function that’s earier to interpret and error-check.
The AND(…) function works in a similar way.
Thanks David for a series of interesting articles and clever alternatives.
“
Brian P points out a gap in our last Select Case statement which starts as:
Select Case RatingCase 4.5 To 10Prediction = “Boom”Case 3.5 To 4.49Prediction = “Recovery”…
If a Rating value extends to 3 decimal places like 4.493 it won’t be caught in the above code because it’s between 4.49 and 4.5 – the Case Else will be applied.
In real life code (as opposed to examples to make a particular point) you have to check that values are to the precision needed. In this some real world solutions could be:
- ROUND() the Rating value to the needed precision eg Select Case Round(Rating,2)
- Or one of the rounding alternatives – ROUNDUP, ROUNDDOWN CEILING, FLOOR, MROUND, INT etc.
- Test the data when entered and ensure it’s in the correct range and precision before being evaluated.
- Use a more flexible alternative to SELECT CASE which can handle greater than or less than eg
IF Rating >= 4.5Prediction = “Boom”ElseIf Rating < 4.5 and Rating > 3.5Prediction = “Recovery”….Here the use of ranges and the >= ensures that no value, regardless of precision, falls through undetected.
