Office Watch

Office 2013

Office Mobile / iPad

Office 2010

Office 2007

Office 2003

Office XP

Office for Mere Mortals

Access

Email

Buying Office

Office 365

Winks

Office News Wire

Join us!

Our Ebooks

Mobile | PDA

RSS


Search

Command Finder


Microsoft Office Bookshop

About

Home




Excel - Nested IF's and alternatives

A look at nested IF statements and alternatives that are easier to make and understand later.

by Office for Mere Mortals

Bookmark and Share

  | Mobile | click for more article services     


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.

Excel - Nested IF - example table image from Excel - Nested IF

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 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:

Excel - Nested IF - lookup table alternative image from Excel - Nested IF

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.

Article posted: Monday, 08 June 2009

there's more ...

If you liked this article you'll LOVE our new ebooks.

Office 2013: the real startup guide

OFFICE 2013: the real startup guide Everything you need to know about Office 2013 but Microsoft won't tell you.

How to save money, install, configure and use the new features in Office 2013.  Get it today - click here.

Windows 8 for Microsoft Office users

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:



Article Services sponsored by: Office Watch Ebooks - available now to download and read today.
RSS feed for this category Subscribe

Translate | Mobile | Links
 Add to: Bookmarks | | DiggThis | Yahoo! My Web


New & Popular
» Using Conversations in Outlook
» OneDrive for Business alters files
» About Outlook Conversations
» Keep using your device on the plane
» Excel Online – changing date format
» Office Online update


Office Watch, Office for Mere Mortals, Access Watch and all titles used within the publications are Copyright © 1996-2014 Office Watch.
Microsoft Office, Microsoft Word, Microsoft Excel, Microsoft Outlook, Microsoft Powerpoint and doubtless many other names are registered trademarks of Microsoft Corporation.

Search  |  Sitemap |  Popular Topics | Privacy Statement |  Advertising |  Twitter |  Feedback / Contact Us
Office Watch is definitely not affiliated with Microsoft - and that's just one reason why we are so useful to Microsoft Office users around the world J (Erko).