A look at nested IF statements and alternatives that are easier to make and understand later.
by David Goodmanson
In my last article I looked at a simple Excel IF statement plus their useful cousins CountIf and SumIF. I also touched on the nested IF statement, where there are many IF functions in one cell.
This time we’ll look at the nested IF in more detail and especially alternatives that are easier to make and understand later.
Following on we’ll look at some alternate ways to utilize the IF statement in some VBA code (Visual Basic for Applications), as well as the Select Case statement.
The rating number in column B is turned into a text description in Column C. In Excel there are all manner of ways to do this. In a simple example like this a nested IF is enough however there are other options that are easier to read by other people.
No-one ever sets out to make a long group of nested IFs. It usually creeps up on everyone over time. A simple nested IF with 2 or 3 options can grow with more and more IFs to the point where it’s unreadable and prone to coding error. Here’s a ‘simple’ nested IF with 40 levels:
=IF(A2="A",TRUE,IF(A2="B",TRUE,IF(A2="C",TRUE,IF(A2="D",TRUE,
IF(A2="E",TRUE,IF(A2="F",TRUE,IF(A2="G",TRUE,IF(A2="H",TRUE,
IF(A2="I",TRUE,IF(A2="J",TRUE,IF(A2="K",TRUE,IF(A2="L",TRUE,
IF(A2="M",TRUE,IF(A2="N",TRUE,IF(A2="O",TRUE,IF(A2="P",TRUE,
IF(A2="Q",TRUE,IF(A2="R",TRUE,IF(A2="S",TRUE,IF(A2="T",TRUE,
IF(A2="U",TRUE,IF(A2="V",TRUE,IF(A2="W",TRUE,IF(A2="X",TRUE,
IF(A2="Y",TRUE,IF(A2="Z",TRUE,IF(A2="AA",TRUE,IF(A2="AB",TRUE,
IF(A2="AC",TRUE,IF(A2="AD",TRUE,IF(A2="AE",TRUE,IF(A2="AF",TRUE,
IF(A2="AG",TRUE,IF(A2="AH",TRUE,IF(A2="AI",TRUE,
IF(A2="AJ",TRUE,IF(A2="AK",TRUE,IF(A2="AL",TRUE,IF(A2="AM",TRUE,
IF(A2="AN",TRUE,FALSE))))))))))))))))))))))))))))))))))))))))
In Excel 2007 you can have 64 nested IFs in one formula, though good luck debugging such a line! Nested IFs are a little easier to control in recent versions of Excel which have formula color coding etc. Despite those helpers, heavily nested IFs are best avoided.
If you think your nested options might be extended later, consider one of the alternatives from the outset.
Nested IF
First, for the Nested IF functions the formula for cell C3 is shown below.
=IF(B3=5, "Boom", IF(B3=4, "Recovery", IF(B3=3, "Turning Point",
IF(B3=2, "Recession", IF(B3=1, "Depression")))))
The nested IF works from left to right evaluating the logical test, e.g. B3 = 5, if that rating is true then IF inserts the text “Boom”. If B3 does not equal 5 then IF looks at the next logical test (B3 = 4) and evaluates each step in turn.
- IF continues to evaluate each test until the logical test is satisfied, and then exits the function.
- The equal sign to the left of the first IF is only required once for that first IF, thereafter, for each level of nesting, we simply put in the text IF. (This is true for any nested worksheet function).
- The number of open brackets “(” must be matched by the number of closing brackets “)”. In the formula example above there are 5 open brackets, matched by 5 closing brackets at the end. Recent versions of Excel have better coloring and error checking features to keep the brackets and quotes straight, but even so nested IFs can be a nuisance to work out.
Lookup Table
One way to get around the complexity of the nested IF is to use VLOOKUP linked to a reference table elsewhere in the worksheet.
Our table would look like this:
The function in Column C of the original table now reads:
=VLOOKUP(B3,$G$2:$H$6,2,FALSE)
Usually you’d put the lookup table in other tab of the same worksheet but the data can be sourced from anywhere.
VBA – the IF statement
An alternative is to make a custom VBA function and put the entire IF logic into VBA code.
This has the advantage of being much easier to read and understand.
The downside is the need for code security and concern that the VBA code is legitimate and not a virus. If your users are not accustomed to getting worksheets with attached code you might want to avoid VBA options.
Here’s a code snippet, it’s the same logic as the nested IF at the start of this article but, as you can see, is a lot easier to read:
' Using a VBA if statement structure
' ==================================
If Rating = 5 Then
Prediction = "Boom"
ElseIf Rating = 4 Then
Prediction = "Recovery"
ElseIf Rating = 3 Then
Prediction = "Turning Point"
ElseIf Rating = 2 Then
Prediction = "Recession"
ElseIf Rating = 1 Then
Prediction = "Depression"
End If
VBA: Select Case
Finally, an even easier structure to read is the Select Case statement.
'Using a Select Case structure
'=============================
Select Case Rating
Case 5
Prediction = "Boom"
Case 4
Prediction = "Recovery"
Case 3
Prediction = "Turning Point"
Case 2
Prediction = "Recession"
Case 1
Prediction = "Depression"
End Select
The Select Case statement is another VBA structure which provides a way of evaluating a range of alternatives with a minimum of repetitive keying required. It is also simpler and easier to read and should be used when there are more than 3 alternatives of the variable of interest.
VBA: and beyond …
Finally to give you some idea of the greater power of Select Case here’s an extended version of Select Case:
Select Case Rating
Case 4.5 To 10
Prediction = "Boom"
Case 3.5 To 4.49
Prediction = "Recovery"
Case 2.5 To 3.49
Prediction = "Turning Point"
Case 1.5 To 2.49
Prediction = "Recession"
Case 0.5 To 1.4
Prediction = "Depression"
Case 0 To 0.49
Prediction = "Money under the Mattress"
Case Else
Prediction = "No prediction - there's no economy left!"
End Select
This version tests for ranges of values (eg. a rating from more than or equal to 3.5 but less than or equal to 4.5) and allows for someone offering a fractional rating like 2.5.
In addition, at the bottom of the structure is the “Else” control. Basically it covers everything else we didn’t cover in our preceding Elseifs. If nothing equates to the rating, then the Else part of the structure provides an alternative control.
Well that’s it for the IF function series. Keep your feedback, questions and suggestions coming in. See you next time.
See Also
- Excel’s SUM is much more
- Making a complex Excel cell easy to use
- More nested IF options
- The CHOOSE option in Excel
- Excel’s IF function
