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 Rating

Case 4.5 To 10

Prediction = "Boom"

Case 3.5 To 4.49

Prediction = "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.5

  Prediction = "Boom"

ElseIf Rating < 4.5 and Rating > 3.5

  Prediction = "Recovery"

….

Here the use of ranges and the >= ensures that no value, regardless of precision, falls through undetected.