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.
Noone ever sets out to make a long group of nested IF’s. It usually creeps up on everyone over time. A simple nested IF with 2 or 3 options can grow with more and more IF’s 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 IF’s in one formula, though good luck debugging such a line! Nested IF’s are a little easier to control in recent versions of Excel which have formula color coding etc. Despite those helpers, heavily nested IF’s 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 IF’s 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.
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.
Article posted: Monday, 08 June 2009
there's more ...
If you liked this article you'll LOVE our new ebooks.

Windows 8 for Microsoft Office users A practical guide the new, changed and unfamiliar in Windows 8
A focused and unvarnished look at Windows 8, especially written for
the many people who use Microsoft Office Get it today
 click here.

ORGANIZING OUTLOOK EMAIL  tame your Outlook 2010 Inbox
100+ pages of practical tips and help to streamline,
automate and search your Inbox. Get more
than you ever thought possible from Outlook. Read it today
 click here.
More from Office Watch:
