I Heart IF

Do you love IF()?
The IF worksheet function in Excel is essential.
In my experience, after SUM and AVERAGE, it is one of the first functions that budding Excel users discover with an Eureka moment.  They start feeling empowered and realize for the first time that they can control Excel and make interesting things happen in their spreadsheet.
IF is very important and sometimes a situation demands that the logic of a compound formula branch, i.e. if some situation, then do this, otherwise do that.
I_heart_if.png

However, IF is also the most overused function by far, especially once the modeler develops the expertise to create nontrivial spreadsheets. Nested IF functions often riddle these spreadsheets, sometimes to very deep levels. Besides making a spreadsheet unnecessarily complicated, there are very real limits to how deeply one can nest IF functions. In Excel versions prior to 2007 this limit was 7 levels deep. Excel 2007 raised this limit to 64, but I would suggest that if you need more than a few levels your approach probably needs tweaking.
So what are some alternatives to IF()?


I’ll give you three, none of which have an arbitrary limit of conditions.
The first two are fairly simple: Table Lookup and Boolean Logic.
Suppose you had a situation where you wanted your formula to use a certain value based upon the contents of the Cell A2. You might have a nested formula that looks something like:
=IF(A2=”red”,25,IF(A2=”white”,101,IF(A2=”blue”,74,0)))
A very simple formula, it results in a value of 25 for red in A2, 101 for white, 74 for blue, and finally 0 for any other color or no color at all. But what if you had more than seven colors where you needed to supply values in this formula. It simply would not work. In fact Excel won’t even let you enter such a formula: when you hit enter it will pop up an error message informing you that you cannot nest the IF function to that many levels and the formula will be summarily rejected (Excel 2007 will let you get away with it, but please don’t). A much better idea would be to create a lookup table somewhere in your workbook with one column for all of your colors and another for all of their corresponding values. Then you could do a simple Vlookup of the table to return the correct value for any color. The table could be extended to thousands of rows and our formula would stay just as concise as it was for three colors. Excel has quite a few functions that can lookup values in such tables not just Vlookup.
The second approach uses boolean logic. The following formula produces the exact same result as our original nested IF formula, but notice that it uses no IF functions at all:
=(A2=”red”)*25 + (A2=”white”)*101 + (A2=”blue”)*74
This is a much more elegant formula. It’s shorter. It’s easier to read. And, it’s easier for Excel to calculate. There is no branching at all. There’s one phrase for each color and the calculation engine just plows through and adds up the totals for each phrase. So how does this work? Consider the following:
=(A2=”red”)
This simple comparison will result in a value of TRUE if A2 has “red” in it (just to be clear: the string of letters, not the background color). If there is anything else or even nothing in A2, then the result will be FALSE. True and False are actual values in Excel. If you were to enter this formula in some cell, you would see the word TRUE or the word FALSE in that cell, depending on what was in A2. It’s kind of interesting when you think that only one value out of an infinite number of values in A2 will result in a True. False will be the result for anything else (is that infinity minus one?)
I need to point out that this type of comparison does not distinguish between upper and lower case, so “Red”, “RED”, “rEd”, and “reD” will all be counted as “red.” If case sensitivity is important in your circumstance you should change the formula to =EXACT(A2,”red”). The EXACT() function will return True only if the two strings are identical including the case of each character. But for now let’s assume that case is irrelevant as it often is.
You’ll notice that our first phrase in my boolean logic replacement formula above has the “*25″ appended. Here we are exploiting the fact that Excel stores the value of True internally as the number 1 and the value of False as the number 0. Let’s assume that the string “blue” was in A2 and watch below how Excel gets to the correct result:
=(A2=”red”)*25 + (A2=”white”)*101 + (A2=”blue”)*74
=(False)*25 + (False)*101 + (True)*74
=(0)*25 + (0)*101 + (1)*74
=74
In practice, since there is no branching in the logic, Excel can calculate this style of formula at lightning speed, and as in the Table Lookup method, there is no limit to the number of phrases (colors in this case) you can include in the formula. This method is potent and will be used extensively in a vast variety of situations here at Excel Hero.
So there you have two fairly simple alternatives to blindly using the IF function. Both have no limits on the number of conditions, and both are much easier to read and easier for Excel to execute. In our mantra of “interactive, faster, shorter, easier to maintain, or simpler” they trump the nested IF approach. If the IF function has made you feel empowered, just wait until you master these two techniques!
Ok. I said I would share three alternative techniques. The third technique is to encode multiple answers into one integer value and to decode them using bit inspection and a bitmask. This is the most potent tactic of all, but it’s way beyond the scope of this post. To understand this advanced technique, please read my post, 5 And 3 Is 1 and then study how a bitmask and bit inspection are used to decode the characters in my LED RSS News Ticker.
Finally remember that IF() is not evil, just precious. There are times when it’s unavoidable, and it would be a shame to hit the nesting limit simply because several layers of unnecessary nesting have already occurred in a formula. Personally, I make a game out of it and pride myself on using as few (mostly none) IF functions as I can. Try it. You’ll soon be treating the IF function as if it were a precious jewel not to be squandered, and as a byproduct your spreadsheet models will be much better.
As a footnote to this post I’d like to point out that prior to Excel 2007, one of the uses of the IF function that was unavoidable was error catching. Say you developed a beautiful custom formula and it produced the perfect result except that sometimes because of inputs beyond your control, the formula resulted in one of Excel’s error values. In addition your model required that the result of your fancy custom formula be used as an input somewhere else, and the error values cascaded causing errors all over the place (error in = error out). The only fix for this type of scenario is to trap the error with an IF function that looks something like this:
=IF(ISERROR(MyFancyFormula),0,MyFancyFormula)
Heinous, to be sure. It requires doubling your fancy formula just to prevent Excel’s error message from laying havoc to our model; and it forced us to use an IF function. Thankfully, in Excel 2007, Microsoft introduced a new function called IFERROR(). So now the Excel calc engine does not need to calculate MyFancyFormula twice just to accommodate the possibility of an error. Here is how the same situation is handled in Excel 2007 and 2010:
=IFERROR(MyFancyFormula,0)

207 thoughts on “I Heart IF”

  1. Hi,
    I think there is a link missing in the article at the end of this para:
    “Ok. I said I would share three alternative techniques. The third technique is to encode multiple answers into one integer value and to decode them using bit inspection and a bitmask. This is the most potent tactic of all, but it’s way beyond the scope of this post. To understand this advanced technique, please read [sdfsdf].”
    Presumably the [sdfsdf] was a placeholder until you went back to put in the actual link?
    Alan.

  2. Hi,
    Can you extend the boolean example to a non-numeric result?
    Tweaking your example:
    IF(A2=”red”,”North”,IF(A2=”white”,”South”,IF(A2=”blue”,”East”,”Other”)))
    I realise that is trivial as an example, but the boolean approach is so much easier to read!
    Thanks,
    Alan.

  3. Hello Alan and welcome to my blog!
    Thanks for catching the missing link. The place holder has been removed and links provided.
    Your question regarding non-numeric output with the boolean approach is timely. It certainly can be done and in fact, I’ve just posted a project to this blog that uses the technique extensively, my Excel 2007 Chess Game Viewer:
    http://www.excelhero.com/blog/2010/02/excel-2007-chess-game-viewer.html
    Here is the example you posed:
    =IF(A2=”red”,”North”,IF(A2=”white”,”South”,IF(A2=”blue”,”East”,”Other”)))
    The one thing that complicates this is the ELSE clause which evaluates to “Other”. Let’s pretend for just one moment that instead of “Other” we were okay with (or even wanted) a blank to be the result of our formula whenever A2 had something other than red, white, or blue. Here is how to eliminate all of the IF functions:
    =REPT(“North”,A2=”red”) & REPT(“South”,A2=”white”) & REPT(“East”,A2=”blue”)
    The beauty of REPT is that it does not return an error for zero repetitions, so the above works great with no error checking. It does so in the same style as my numeric output example in the main post; there is no branching. The calculation engine marches straight through, concatenating the strings of text, including the null strings where the tests fail. If a test passes, that REPT is given a repetition input of just “1”.
    I think this is easier to read than the nested IF approach.
    Now in a lot of circumstances having a blank output if none of the tests are true would be perfect.
    HOWEVER, to fully satisfy your example, we will need to extend the formula:
    =REPT(“North”,A2=”red”) & REPT(“South”,A2=”white”) & REPT(“East”,A2=”blue”) & REPT(“Other”,(A2<>”red”)*(A2<>”white”)*(A2<>”blue”))
    The last test clause on that formula will return “Other” if anything else (or nothing) is in A2.
    Finally, I’ll point out another way to craft the final test clause:
    REPT(“Other”,NOT(OR(A2={“red”,”white”,”blue”})))
    This is a little shorter and showcases an interesting way to use the OR function with an array of constants.
    Thanks for commenting. I’m trying to add useful information to this blog every week. Hope to hear from you again!
    Regards,
    Daniel Ferry
    excelhero.com/blog

  4. The Zune concentrates on being a Portable Media Player. Not a web browser. Not a game machine. Maybe in the future it’ll do even better in those areas, but for now it’s a fantastic way to organize and listen to your music and videos, and is without peer in that regard. The iPod’s strengths are its web browsing and apps. If those sound more compelling, perhaps it is your best choice.

  5. First thanks a lot for IFERROR function recomendation (SI.ERROR en español), I use it a lot and it’s a great implamentation in my worksheets. In second place, I don’t use nested IF like you show above, I use VLOOKUP (BUSCARV en español).
    Henry Rios.

  6. @Daniel
    In your reply to Alan, you pointed out in your “another way to craft the final test clause” section, this…
    REPT(“Other”,NOT(OR(A2={“red”,”white”,”blue”})))
    I think you can save a function call (the NOT one) by doing it this way…
    REPT(“Other”,(AND(A2.NE.{“red”,”white”,”blue”})))
    NOTE: I used .NE. in place of the “not equal” symbol (“less than” symbol followed by “greater than” symbol) because your comment processor appears not able to print the “not equal” symbol.

  7. @techcircle-
    Welcome to my blog. I’m glad you like it here. I’m trying to make it a useful place for Excel learning.
    Regards,
    Daniel Ferry
    excelhero.com/blog

  8. Hi Daniel,
    I understand about REPT and other functions to be used insted of If(). I have been using if() function alot until I read above article and I am trying to change all the old formulae with if(). I need some help with the following one.
    if(a2=”X”,if(a2>25,some crazy function,someother crazyfunction),if(a2

  9. Thanks for the post, good info. It’s hard to find useful information these days. Every blog or site you go to is just copy posting the same old content just to be “blogging”. It’s refreshing to hear something different, no matter the topic. Anyways thank you.

  10. @techcircle-
    I’d be happy to help you.
    Could you please provide the exact formula that you are trying to convert. The answer may depend on which “Crazy” functions you are calling.
    Welcome to my blog!
    Daniel Ferry
    excelhero.com/blog

  11. Here is the formula I was taking about:
    IF(C6=”(K)”,IF(D6>265,IF(D623,’Cal Sheet’!D32,’Cal Sheet’!C32)),”ITOR”),”ITOR”),IF(D6>1200,IF(D623,’Cal Sheet’!H32,IF(D6

  12. @techcircle-
    Your formula is not quite correct, because it’s not getting pass Excel’s parser – meaning I can’t enter it into a cell.
    But, looking at it, I get the gist of what you are trying to do.
    Look at this formula:
    =REPT(“ITOR”,(C6=”(K)”)*(D6<266)) &
    REPT(Cal!D32,(C6=”(K)”)*(D6>265)*(D623)) &
    REPT(Cal!C32,(C6=”(K)”)*(D6>265)*(D623=FALSE))
    That only covers three of the conditions you need, but I think you can see the pattern and extend it to meet all of your conditions.
    Just think of each condition as it’s own little formula and place all of the boolean comparisons that that little part needs to evaluate to true after the comma in each REPT function. Then all you do is keep adding another self contained REPT function for each compound condition. When most of them are FALSE, those particular REPT functions will return a null string (essentially nothing).
    So the final total output is just from the one REPT function that passes all the tests.
    Regards,
    Daniel Ferry
    excelhero.com/blog

  13. Dear Daniel,
    Thank you very much for the suggestion, I have changed my formula.
    Before,
    =IF(C6=”(K)”,IF(D6>=265,IF(D6=23,G12,G13)),”ITOR”),”ITOR”),IF(D6>=1200,IF(D6298))
    &REPT(G12,(C6=”(K)”)*(G6>=23)*(D6>=265)*(D6=265)*(D6

  14. @techcircle-
    The reason your entire formula did not appear in your comment is because you did not have any spaces in it. The blog software treats such strings of text as one really long word and does not know how to parse it or wrap it.
    Just enter some spaces in your formula (after a comma for example) and it will display properly on this blog.
    Regards,
    Daniel Ferry
    excelhero.com/blog

  15. @Loren-
    Wow. Nice to hear from you!
    Everyone in our family is doing very well. How are A, R, S, B, and K?
    Regards,
    Daniel Ferry
    excelhero.com/blog

  16. All are doing pretty well; since the last time I saw you was (I think) at Sheryls’ wedding, we could probably both write novels on family events since.
    Nice site. Mine is somewhat less than as intellectual; was born from boredom during a temp. unemployment period that just ended today.
    Fun stuff http://dotloren.webs.com/
    nice hearing from you too.
    Loren

  17. Daniel…This was great. I’ve been guilty of both building non-trivial worksheets AND overusing the IF functions. So thanks.
    I guess you could also use the CHOOSE function, in combination with a VLOOKUP or a MATCH function.
    e.g. =CHOOSE(MATCH(A2,B2:B4,0),25,101,74)
    with the colours Red, White, and Blue stored in B2:B4. To cope for errors (i.e. a user enters ‘black’ in A2) you could either use data validation pointing to a list of permissible colours, or you could wrap your function in a pesky IFERROR function thus:
    =IFERROR(CHOOSE(MATCH(A2,B2:B4,0),25,101,74),”please pick an allowed colour for your excel blog theme”)
    Or you could use the SUMPRODUCT function:
    =SUMPRODUCT(–(B2:B4=A2),C2:C4)
    …with the corresponding values stored in C2:C4.
    As you’ve pointed out before, Excel offers us an embarrassment of problem-solving riches.

  18. This is great. Comparing different methods or approaches to the same problem is a good way to gain an in-depth understanding of a technology. Typically information regarding Excel is presented in the form a quick, one-dimensional tip. While those can be handy, I’m always left wanting something more substantive. One can only go so far by learning a bunch of isolated tips.
    Oddly, I use reference or lookup table as well as the Boolean approach when programming, but never made the connection to using this within Excel formulas. I think this is because I prefer to embed complex business logic within code where, at least for me, it is simpler to organize and understand. Typically I’ll dump the processed data into Excel, using it primarily as visualization or reporting layer.

  19. Daniel,
    there is another way around the level of 7 nested Ifs in versions prior to 2007, you can either use =concatenate(if(a1=”a”,1,””),if(a1=”b”,2,””),if(a1=”c”,3,””), etc etc, this will give you up to 30 different conditions to test, if you use the concatenation operator (&) as in =if(a1=”a”,1,””)&if(a1=”b”,2,””)&if etc etc this does not limit you at all, however your boolean multiplication method is a lot cleaner to look at and allows anybody else analysing the formula to see what is being connected, just thought I would share this with you.

  20. Hello Daniel,
    Great review of alternate-to-If methods. I’ve spend a few minutes to benchmark each one (with 5 values) , and here’s the result :
    – Fastest : IF
    – 2nd : CHOOSE(MATCH())
    – 3rd : REPT&
    The code in VBA is :

    start = Timer
    For i = 1 To nbMax  ' nbMax is set to 10.000
    Range("D3").FormulaLocal = "=REPT(""TOTO"";A2 = ""Blanc"") & REPT(""TITI""; A2=""Rouge"") & REPT(""TUTU""; A2=""Vert"") & REPT(""TATA""; A2=""Bleu"") & REPT(""TETE""; A2=""Noir"") &  REPT(""--Choisissez une valeur--"";ET( A2>{""Blanc"";""Rouge"";""Vert"";""Bleu"";""Noir""} ))"
    'Range("D4").Calculate
    Next i
    Range("B3").Value = Timer - start
    ' and repeat for each case...
    

    The results are :

    Time(s)x10.000     Method
    8,4531250          REPT()
    8,1093750          CHOOSE(MATCH())
    7,0781250          IF()
    

    Regards.
    Cyril

  21. I forgot to say the time includes parsing and calculation.
    If I replace the FormulaLocal by only a calculate call, REPT is faster, then CHOOSE and IF at last. But this is really close.
    here are the results :

    Time (x 1Million)   Method
    41,0625000          REPT
    41,2148438          CHOOSE
    43,1250000          IF
    
  22. As a user of Excel 2003, is there a way to write an iserror() formula in VBA that would load everytime Excel is open so I have that functionality available to me without upgrading to Excel 2007?
    Love your blog

  23. Hello.
    I think you want IFERROR (not iSerror). You can indeed roll your own for previous versions. Here is the VBA:

    Function IfError(formula As Variant, show As String)
    On Error GoTo ErrorHandler
    If IsError(formula) Then
    IfError = show
    Else
    IfError = formula
    End If
    Exit Function
    ErrorHandler:
    Resume Next
    End Function
    

    This should be placed in your Personal.xls file. If you don’t have one already, you can create one (it just a normal Excel file with the VBA routine in it).
    If you have Office XP, it should go in this folder:
    C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART
    In other versions of Excel it should go here:
    C:\Program Files\Microsoft Office\Office\XLStart
    Now when you use Excel, that function will be available in all of your workbooks.
    Of course you could install it as an Add-In, as well.
    The VBA code came from this Microsoft Knowledge Base article:
    http://support.microsoft.com/kb/280094
    Hope that helps.
    Regards,
    Daniel Ferry
    excelhero.com

  24. There are couple of things that can be done with IF that cant be done with boolean functions
    =IF(Rng=”Something”,Row(Rng))
    Will return an array of Numbers or False.
    This cant be replicated using any thing else.
    We can get an array of Numbers and Zeros but not Numbers and False.

  25. Excel Hero Academy Homework
    Jose Alberto Carranza
    I most confese I used to be a nested if guy. My formulas are usually kind of long but after reading this, for a lack of a better word, mind blowing post, I’ll start using boolean operators on my formulas since this will make my files not only run faster but also take less HDD space.
    Well, once again thank you so much for this knowledge which just confirms that there actually isn’t anything quite like this!!

  26. I can’t believe I haven’t used the IFERROR() formula by now….it will definitely find its way into my projects – and hopefully fewer nested IF functions!
    Thanks!

  27. I actually learned about this technique many years ago from Aaron Blood (XL-Logic.com), but have never made any serious attempt to avoid IF formulas until now. I have just now completed the assignment_01_sales_commission_formula with boolean logic instead of IF. What a delightful feeling (even if my formula is somewhat long compared to the best).
    Daniel – I also very much liked what you wrote February 14, 2010 on the REPT function. Thank you. Now I amm looking forward to the ‘5 And 3 Is 1′ article.

  28. @Sam or Daniel: Regarding Sam’s comment about =IF(Rng=”Something”,Row(Rng)) giving an array of numbers or False…this is interesting. I was wondering if either of you can think of another interesting application for this beyond showing a user either a number or the word ‘false’

  29. As many other Excel users, I have been doing loads of terrible Nested If formulas over the years, mainly because it was the only technique I knew. Well, that is now coming to an end!
    I really like the boolean logic for its simplicity – it’s lean and clean, and I guess it will really speed up calculations as opposed to formulas containing multiple Nested Ifs. So I am looking forward to starting applying this method. I think it can also become a considerable time saver in so far as it provides easier administration and errorhandling!

  30. Just revisited the sales commision assignment in the academy, and boolean logic makes it easier to read than nested if’s, once you understand what’s going on at least. Throw named formulas (ranges) into the mix and it is a lot easier to understand as well.

  31. I remember reading I heart IF and The Venerable Sumproduct earlier this year when I first discovered Excel Hero. It was at this time I realised how little I really knew about Excel. Since then I have put the Sumproduct to good use, but have to admit, I’ve slacked on trying to avoid using IF. I will endeavour to cut back on IFs during Excel Hero Academy.
    I also love REPT().

  32. I do not understand the comment/example works. If I name a group of cells rng , c1:c10 for example, and type “Something” in C1 the formula returns 1. If I move “something to any other cell in that range False is returned. Can you explain a bit more or give an easier example for me to understand? Thanks

  33. I am guilty as charged – in fact today I found a nested IF that I should be really ashamed of (maybe I’ll show you in class the extent of what I was capable of before taking your class).
    I am now trying to rework this example with some arrays and named formulas.
    Onward and upward.
    Oli (EHA student)

  34. Daniel –
    This post changed my excel life fundamentally. It was first extremely confronting, after which grudging acceptance, and now finally a deep appreciation.
    I think your rule of using IF only once in a given formula is perfect. If it needs a nesting, then it needs a boolean for me.
    However, im wondering about calculation speeds when you compare a single IF statement with a boolean alternative. Say i have a formula that USUALLY is one value, along the lines of =if(a=2, “yes”, “no”). Since a is only infinity minus 1 times equal to 2, it usually calculates as false, or “no”. My theory is that its better in this case to reverse the statement to =if(a>2, “no”, “yes”). When i watch this in the evaluate function i notice that if the first part of the if evaluates to false (which it usually does) then excel doesnt bother to even calculate the ‘true’ portion of the formula. If i have a complex formula (i often have an index lookup there) then this may be slowing my sheet down if i have thousands of this style of formulas.
    My guess is that it is better to use this ‘usually false first’ style of IF formula instead of the boolean alternative of =rept(“yes”, a=2) + rept(“no”, a>2) because excel only calculates the a= portion once rather than twice as in the boolean.
    Does this understanding of the calculation engine in excel seem right to you?
    Jesse Warburg (from the Excel Academy doing his homework)

  35. I was first introduced to using the Boolean logic in this fashion with EXCEL when reading John Walkenbach’s books. I definitely understand the concept and use in my models.
    From a personal viewpoint I have a “rule” I made up—“Max nested If’s I can use is 3″. Therefore I force myself to find another excel function or combination of functions to use instead of nesting If/Then statements.
    If the possible conditions lend themselves to a definitive numerical range then I may use the CHOOSE Function. A good example is if your evaluating the day of the week then I would use the combination of WEEKDAY to evaluate the IF and the CHOOSE to provide the 7 possible THENS.
    Also when the possible results are less then 10 I like to embed the lookup table in my formula as an array—so I can see them in my formula.
    IFERROR(VLOOKUP(A2,{“red”,25;”white”,101;”blue”,74},2,0),0)
    Daniel, your approach to explaining this concept is elegant and thought provoking. I will change my rule to 2.

  36. It always felt so ham-fisted to use nested ifs, I’m relieved to learn there are better ways.
    I love the simplicity of the summed / concatenated boolean comparisons approach, but the reflex of using If() will take some practice to retrain.
    I also learned something new in that comparison notation can be used outside of the If() function.
    Rept() for null string upon comparison false is also wonderful to now know about. I have definitely overused the form of If(A1=””,””,SomeFunction(A1)).

  37. HI Daniel
    I use IF() all day long, but can be messy. Certainly opened up new horizons. Even more anxious now to get in working on the Excel Academy.
    Bruce Woodroffe (EHA Student)

  38. Daniel
    Tks for the post. Now I realize I know nothing about excel. It’s rewarding to see things now from another perspective
    (EHA Student)

  39. Thanks for the great article! I am guilty of using nested If statements. I have gone through many of my spreadsheets and changed these to boolean. Hopefully this will speed up my updates.

  40. Daniel,
    Terrific article. Substantial information, clear and understandable. Not the least overwhelming. Your gift for Excel is one thing, but your writing ability just burns away the fog!
    (EHA Student)

  41. I must confess that I’m an IF() addict, but I’ve learned that it’s much better in most cases to do some sort of lookup. If realistic.
    One if function I on workbooks that display a full year by month. I process and place all the raw data on a hidden sheet, then do various lookups and SUMIFs to get the data. However, for months beyond the current month, I don’t want 0’s, I just want blanks. So I always do a check on the current data date and compare to the column date then execute the formula.
    Like this:
    =IFERROR(IF(M$3″

  42. ok…that formula got a bit jacked thanks to html which is NOT one of my strong points.
    =IFERROR(IF(M$3<asof,M11-M12,””),0)

  43. Nested ‘If’ used to be my trade mark (I’m ashamed to say). I discovered the boolean logic technique as part of the SumProduct approach to dealing with multiple criteria sums, though still find it tempting to go back to If when it isn’t necessary. I’ll be working on that. EHA student and loving it.

  44. Awesome stuff here… in terms of simplicity what you have shown is great, is there a perfomance hit among the different methods or they are pretty much similar with regards to that?
    Sriram(from the Excel Academy doing his homework)

  45. Good stuff!
    I am very guilty of nesting IFs to the extent that I completely forget what I was trying to achieve. That said, when I’m thinking straight I also use the methods recommended here – especially when I’m working within Xcelsius.
    Iain

  46. Definitely a new thought process to get on board with. I can think of many instances where this will work brilliantly for me, but I do have a problem case, with respect to a recent formula I created.
    “=IF(C5+D5=0,”Not Held”,IF(C5=D5,”Constant”,
    IF(AND(D5>C5,C5=0),”New”,IF(AND(D5C5,D5>0),
    “Increased”,IF(AND(D50),”Decreased”,
    “Error”))))))”
    “=REPT(“Not Held”,C5+D5=0)&REPT(“Constant”,C5=D5)&REPT(“New”,AND(D5>C5,C5=0))&REPT(“Eliminated”,AND(D5=0,C5>0))&REPT(“Increased”,AND(D5>C5,C5>0))&REPT(“Decreased”,AND(D50))”
    What these two formulas do is basically categorize two position sizes. So if I didn’t hold any on date 1 (in C5), and did have a position on date 2 (in D5), then it classifies it as a new position. Using the REPT methodology certainly makes the formula more understandable, but I am unclear which is the best method in this case. The REPT formula is longer by 10 or 12 characters. My IF formula is shorter and has an error else segment. Is there a solid case for using one over the other in this instance? Is there a simpler, clearer way that I am missing. No biggie, I have my solution–just trying to think about it from a different perspective.
    Michael Pennington
    Excel Hero Academy Candidate
    Sorry, I have no idea how to use HTML tags to make the formula viewable.

  47. Hi Daniel,
    Reading this article was how i discovered excel hero in the first place and convinced me to join the EHA.
    I absolutely love the Boolean logic.so much easier
    to use.
    Martin Logtens EHA student)

  48. Daniel,
    very nice post. I used lookups alot instead of if(), but the boolean logic is new to me. Looking forward to try it, if I don’t want to include a lookup table in my workbook.
    Tim

  49. I frequently use formula like =A1=A2 to ensure things are balancing. This displays TRUE or FALSE in the cell and then I use conditional formatting to highlight the FALSE entries.
    You can then easily check a large range of these formula using COUNTIF to see if there are any FALSE items eg =COUNTIF(Range,FALSE)>0
    Neale Blackwood EHA

  50. Thanks for the great article, you’ve convinced me.
    And I think I now finally understand what Roger Whittaker means when he’s singing:

    Oh I don’t believe in If anymore
    If’s an illusion, If’s an illusion
    No I don’t believe in If anymore
    If is for children, If is for children
    Building daydreams

  51. Somehow I deeply feel nesting IFs were something to avoid (when knowing how to). I already use the first option, but the second one is a brilliant new discovery for me. Thanks a lot.
    (Excel Academy student)

  52. All the work i’ve been doing in Excel seems so beginner after reading this…I’m an If abuser it turns out and now, I’ve got to try and quit cold turkey. Oh boy! You’ve certainly challenged all i thought I knew about Excel.
    Thuy

  53. Love the simplicity and readability of the Boolean evaluations instead of those nested IF()s. Now I need to go back and clean up a bunch of spreadsheets…
    Mark Voge (EHA student)

  54. I’ve been if love with “IF” for a long time. But I think I’m going to start two-timing on her now that I’ve met Boolean.

  55. A game changing article….. For the last 6 months I have played the game to avoid using “if” statements as far as possible thereby improving the models that I have created.
    Cheers,
    Michael
    Excel Hero Academy Student

  56. Daniel,
    I’m late getting to my homework (it has to be weekends for me), but I have to say that this is simply BRILLIANT. I admit to using IF statements more than I should. And I discovered the vlookup option a few years ago. But the boolean alternative is simply brilliant!
    And I routinely had been using the IF (ISNA (vlookup…. option to remove the #N/A responses from my queries. I love the IFERROR function and will start using it immediately!
    Thank you !!!
    Janice Duffney

  57. I believe up until this point I have been an over-IFer…
    Scott Wiltshire – Academy homework

  58. I remember many years ago discovering IF then nested IF then cursing nested IF during a maintenence exercise. It just shows that it’s important that you understand what’s ‘under the hood’ of excel plus cultivate creative thinking in order to get the best out of excel.
    Andy

  59. I use lots of if’s. The main one I use is IF(A1=”n”,””,AnyThing I need to do). I’m sure there is a better way, but I have not figure that out yet.

  60. Very interesting alternatives to using IF. Will have to try both in future. It is useful to have these alternatives to nested IFs
    Excel Academy Student

  61. Great read. It’s definitely nice to have another way to do things, especially when it speeds up the process and helps keep file sizes down. Also, I will have to try out the “IFERROR” function, as I’ve definitely retyped “my fancy formula” several times in one formula to account for errors. Thanks, Daniel!
    Tom Quist (from EHA)

  62. As an Excel 2003 user, I have just installed your IfError Funtion as an Add-In. Now I can take advantage of this new function in my error trapping formulas. Many thanks, Daniel.
    M Rettenberger

  63. Nice article. You can use”CHOOSE” function as well to the alternative of “IF” function.
    =IF(a1=1,”blue”,IF(a1=2,”green”))
    =CHOOSE(a2,”blue”,”green”)
    Vinod

  64. Explained carefully for the not so familiar with excel thank you
    cannot make all the formulas work on excel e.g.
    =REPT(“yes”, l15=2) + REPT(“no”,l15>2)
    stepping slowing towards “herohood”

  65. Read and understood, I already used the vlookup method but the boolean logic is new to me and seems extremely powerful.
    Thanks !
    Andrea
    (excel hero academy student)

  66. @Richard Kehl,
    I don’t think there is a better way to express the relationship in this formula than what you have now…
    =IF(A1=”n”,””,Anything you need to do)
    (Note that in all that follows, I am using .NE. to mean the “not equal symbol” of a less than sign followed by a greater than sign because this blog site’s comment processor doesn’t seem able to print the “not equal symbol” correctly, or at least I don’t know how to make it do it correctly)
    However, using this blog articles suggestion, you could write it like the following…
    =REPT(Anything you need to do,A1.NE.”n”)
    Note the flipping of the logical test from equal to not equal (which is why I think the IF version is better… the logic is what you would expect). Another possibility (using the CHOOSE function Vinod suggested)…
    =CHOOSE(1+(A1=”n”),Anything you need to do,””)
    And here is one final way using an unorthodox approach…
    =LEFT(Anything you need to do,999*(A1.NE.”n”))
    One note about the REPT and LEFT versions I posted… the both return text values even if the “Anything you need to do” part returns a numeric value, so you would need to involve them in a mathematical operation that doesn’t change their value in order to force a pure numeric return value. Using the REPT version as a basis (you would do the same for the LEFT version), that would mean something like one of these…
    =–REPT(Anything you need to do,A1.NE.”n”)
    =1*REPT(Anything you need to do,A1.NE.”n”)
    =0+REPT(Anything you need to do,A1.NE.”n”)

  67. This makes really easy to review formulae, once you know the trick. Hey, it might be tough for non-power users to figure out what a ‘nested ifs’ formula does, but at least it’s possible. Boolean logic is even more difficult, let alone REPT. I am tempted to use it though :)

  68. This makes really easy to review formulae, once you know the trick. Hey, it might be tough for non-power users to figure out what a ‘nested ifs’ formula does, but at least it’s possible. Boolean logic is even more difficult, let alone REPT. I am tempted to use it though :)

  69. I saw boolean logic long time ago, but never used it in practice, obviously I didn’t have need for too many nested IF functions. As a substitute I used VLOOKUP and CHOOSE.
    Anyway, as all of your topics, this one is also kind of different and refreshing!
    Thanks Daniel
    Drazen (EHA student)

  70. I echo the feelings that have been expressed over an over about the quality of your blog. It is indeed excellent!
    After reading I love IF(), I tried to implement some of your teachings into a formula that I am currently using and it’s working: G12=IF(OR(D12={“Approved”,”Denied”}),VLOOKUP(“*”&P12&”*”,LookupRng,6,FALSE),””)
    Revised Formula: G12=OR(D12={“Approved”,”Denied”})*VLOOKUP(“*”&P12&”*”,LookupRng,6,FALSE)
    What’s happening is that I no longer get an empty G12 cell whenever D12 doesn’t contain either “Approved” or “Denied”
    How can I make this same operation without the IF(). Based on the examples you gave and the discussions afterwards, it’s not clear to me how can a function be used after a condition is tested.
    Very likely a future EHA student!

  71. Daniel,
    Thanks for your reply!
    Could you show a situation where there is no other alternative but to succumb to the IF() curse!

  72. Daniel,
    Is there any way to use any of the strategies you outline above to take away the need for the IFERROR() statement?
    While IFERROR significantly helped optimize formulae in many of my spreadsheets, many of my users are still using older versions of Excel. So I am forced to use the old method of IF(ISERROR(formula),””,(formula)).
    Bijoy Mathew
    EHA2

  73. This is great! To exercise your points more fully I will start trying to replace my IF statements with the replacements you’ve mentioned. Hopefully, this will also help me do better in EHA.
    I do like to use the If statement, but if there is a more efficient way of doing something I will at least give it a shot.
    The bitmask may be above my skill level, but I have heard it mentioned at work, so I am going to take a look it.

  74. Excellent article! I agree that I really hate it when people use tons of IF statements in one formuala, for they can become extremely hard to follow. Great explanation on alternatives.
    Bob N.
    EHA2 Student

  75. Another great example of “outside the box thinking”. We all get stuck in “our” ways of setting things up in Excel without taking a step back to consider alternatives…that are often better.
    -Chris
    EHA2

  76. I read this article early this spring, but since I rarely use the IF more than 3 deep, I didn’t take the time to full digest the content. I will now. – ajp EHA2

  77. OK,it was IF that made me fall in love with Excel in the first place :)
    I learned to get around the 7 levels issue some time ago!
    I do make use of lookups, on occasion, one of the only times I ever use the ‘1’ option at the end of my vlookups!
    I know that nesting isn’t always elegant, but these examples all assume you want to return a string or numerical result from the if
    Very often, I would use the if statement to determine which other function to use, which this doesn’t seem to help with (or am I just not seeing it?), for example:
    if(a1>1000,vlookup(a1, data1,2,0),vlookup(a1,data2,2,0)

  78. Daniel
    Just read both blogs – kept with them until half way down bitwise at which point began to lose the plot.
    If there is anyone else out there starting module 5 but, like me, is constantly revisiting earlier ones to make them stick please let me know I’m not alone. I suspect that I will be upgrading the subscription just so that I can eventually commit this (brilliant) stuff to memory. Each module introduces so many concepts keeping up is a real struggle as there is so much to learn.I feel guilty starting module 5 as I’m still working on the homework from weeks ago – is that a sin?

  79. Hi Daniel,
    I do like this but surely much easier to use SUMPRODUCT..?
    =SUMPRODUCT((A4:A11=A2)*B4:B11)
    Where the content of A2 is the colour you want to choose.
    Thanks,
    Dan

  80. Hi Daniel –
    I can see a lot of uses for the Boolean Logic method to replace my prior tendency to lean on ‘IF’ too heavily. Excellent description, I’m finding the EHA course to be very valuable, content unlike any I’ve been able to find elsewhere.
    with gratitude,
    Bryan
    EHA2 Student

  81. Excellent article as usual. I do tend to overuse IF statements in my workbooks and this presents an attractive alternarive.
    With scaling values, would IF be preferrable?
    For example,say you have a sales table, and if sales are less than 5000 you show a comment like “Bad” ; if between 5000 and 10000, you show “Good”, and greater than that, “Excellent”.
    With a sales figure in B7, a nested IF as follows does the trick:
    =IF(B7=5000,B7=10000)
    Martin
    EHA student

  82. Great article. I read it three times when I first discovered this blog, and it changed the way I use Excel. I really enjoy the logical and visual ease of using boolean logic in my formulas, and I pretty much avoid using IF unless it is for a very simple either-or formula.
    jnoble
    EHA2

  83. For some reason my formulas in my previous comment got messed up.
    I’ve trued inputting them again now without success. Is there something I should be doing differently when entering formulas in the comment box?
    Martin

  84. I’ve always used booleans before I ever used an if-statement. Maybe, if I’d started out in Excel, things would’ve been different.
    Simon, EHA2

  85. Thanks, I’m a SQL developer and this is much closer to the way I approach problems. I was never very comfortable with nesting IFs in Excel. It just seemed messy and hard to follow.

  86. You are not alone.
    I’m still reviewing the early video’s too.
    Whenever I’m working on the latest concepts and run into arrays or something from earlier lessons I have to backtrack a bit.

  87. I have used Table Lookups and If statements to perform these calculations in the past but never knew about how to use Boolean Logic.
    Dantheman
    EHA2

  88. I’m a student in EHA2.
    Long ago I found someone else’s example of using Boolean logic. Their explanation sure didn’t cut the mustard. This one sure does! I have a need to effectivly perform a VLOOKUP without being able to use VLOOKUP. I used Boolean logic to create a formula that does it instead and this article is what taught me how to do so. Just a note – my formula ended up being over 800 characters in length!

  89. Great post!
    Why does Microsoft and many 500+ page books on Excel not promote this very practical approach to Excel? I have spent years without that fundamental knowlegde.
    Michael Rapp
    EH2

  90. Despite the fact that I have read this post for the nth time I know I will keep coming back for more. Excellent stuff.
    Haider EHA2

  91. I have often used nested IFs and the Boolean Logic approach makes the formulas so much more readable. Thanks
    David Binns – EHA2

  92. I’ve been over-relying on “IF” for years. I’m going to take the challenge and try to come up with other ways to accomplish things (just to see if I can do it).
    This blog also has me wondering if there’s a way to use this logic when a date is buried in a text field. The situation below is coming from Google Analytics data. I’m trying to get the date out of it, but have only been able to get it if the month name is hard coded in the formula.
    Formula in cell A15:
    =DATEVALUE(RIGHT(B15,LEN(B15)-SEARCH(“October”,B15,1)+1))
    Value in cell B15:
    Monday, October 17, 2011
    Does anyone have thoughts on how the info in this blog could be used to get rid of the hard coding?

  93. @Secant7,
    I am guessing that the value in B15 is text and not a real date formatted to look the way you showed us. Given that, give this formula a try…
    =1*MID(B15,FIND(” “,B15)+1,99)

  94. Hi Daniel,
    I’m behind, but finally here. I read it and am really, really loving this Database Module so far. I’ve gone through most of module #3 and #4 as well, but priority dictrates that I spend some time in #5 before going back!
    Thanks again,
    Mike

  95. Daniel:
    Scienceguy from EHA 2 commenting – good article, and one that I will recommend to at least a couple of people who have recently discovered the IF function…
    I like the boolean approach (not a big fan of lookups) and will practice this a few times moving forward.
    Nice article for the beginner *and* the old salt…
    Kevin

  96. I began to think that without IF() I could not finalize a formula while using COUNTIF, INDEX, MATCH and VLOOKUP and now I see that I can reduce the size of the formula with the use of the REPT() or SUMPRODUCT(). Thank you, I can promise myself to use it as soon as…

  97. Read the article as part of the homework, EHA3 Daniel. I’ve read this before, but not used the boolean system instead of IF. Why not! It’s so simple! My mission for the next few weeks is not to use the IF function other than catching errors!

  98. Using boolean expressions readability is definitely enhanced but it could be tricky if your users do not understand the numerical value of TRUE/FALSE

  99. In my former job, programming PLC’s, I used a lot of boolean logic and I wonder why I always use IF when working in excel. Boolean logic is so much easier! Thanks! (EHA3Mod5)

  100. EHA3 Homework: Re-reading this article.
    I often needed to use the construct:
    =IF(ISERROR(MyFancyFormula),0,MyFancyFormula)
    and didn’t realise until Excel2010 that I could have been using the built in IFERROR. I now use my own IfError UDF for Excel 2003 applications.
    Stephen Crump

  101. I have enjoyed the “game” of using these techniques since first reading this several months ago. I really like the boolean logic!
    Ken, EHA3

  102. EHA3 Homework
    I do love IF. I use it to much but with learning the teachings in the academy I am learning better ways.
    Thanks Daniel.

  103. Your first comment about the use of the REPT function for non-numeric evaluations is great, and could be another article on it’s own.
    Thanks
    EHA3 Student

  104. Daniel I have read this fantastic article several time now – it changes your (excel) life! I used IF to it’s limits before this article, and not being from the professional IT background just was not aware that Boolean logic was there way before IF.
    Cheers
    John

  105. Great article and very informative….
    i have used IF many times….and am used to it…but good to see the other side of it by using boolean logic….especially the truth table is amazing…..
    Mohammed Mustafa
    EHA 3 student

  106. Very helpful to understand the foundation of Excel being boolean logic and the need to switch my mindset to thinking in boolean terms

  107. I guess 70% of people, that are new to excel, starts writing =IF(…) Before thinking about the problem. As you mention above, This results in the ‘Overuse’ of it.
    Guilherme Brito – EHA3

  108. I’ve been writing Excel IF() formulae for many years, some of them very intricate. One large workbook got so messy I ended up writing VBA because it was much faster than having a formula in each cell.
    These alternatives are excellent and I will doubtless use them in the future.
    John – EHA3

  109. Nice article. I got a lot about using Logic AND / OR, =, >,

  110. “In practice, since there is no branching in the logic, Excel can calculate this style of formula at lightning speed”
    The fact that this method ((A2=”red”)*25…) is clearer than nested IFs was decisive for me, even without knowing that it was faster.
    Unai (EHA3 student)

  111. Good Article, using logic instead of IF was a time saver.

  112. Love the mantra of “interactive, faster, shorter, easier to maintain, or simpler”
    I’d just add “& do it once!”
    Having inherited so many ‘if’-laden workbooks over the years what you say is SO true Daniel. Thanks.

  113. I’ll use If(ISERROR) no more! Many times my fancy formulas are queries into our SQL database and can be quite cumbersome. IfError YAY!
    I also know now how some were able to come up with solutions to challenge 1 without IF!

  114. Great information about the IF function. I’ve said to my students that really once they get beyond 2 or 3 ifs – they should be looking at using a Vlookup table and it’s great to have that confirmed. Also really like the new IFERROR function – it’s incredibly helpful. I haven’t tried this Boolean logic function but it looks really powerful…and this is part of my homework for Module 5 :-) Anne

  115. Interesting article Daniel. Tend to shy away from nested IF statements where possible as it often ends up quite confusing for the user.

  116. Good insight. My problem with IF is the misunderstandings that occur when my colleagues go to use and edit. Putting the focus on Boolean logic techniques should be a fundamental strategy Excel geeks use. Ultimately this is just another great lesson learned at Excel Hero Academy so thanks Daniel!

  117. Good insight. My problem with IF is the misunderstandings that occur when my colleagues go to use and edit. Putting the focus on Boolean logic techniques should be a fundamental strategy Excel geeks use. Ultimately this is just another great lesson learned at Excel Hero Academy so thanks Daniel!

  118. I’ve always avoided nested IF statements because I get lost in them. I had never seen anyone use Boolean logic in Excel before – it’s a very clean solution. Thanks for the information.

  119. This article was a game-changer for me. I had written a macro (with my caveman coding skills) that took 30 minutes to process. I rewrote the entire program without using a single if statement, and instead using the above methods and got the time down to 50 seconds! The proof was in the pudding. I not a programmer by trade, but I’ll get my hands dirty when I have to. This site, and guides like this, make my life so much easier and more productive! Thanks!

  120. Daniel,
    Just discovered your excellent blog through a link to this topic. As a confirmed over-user of IF() because of not knowing any better I’m keen to change my ways, but as is so often the case it seems there are pitfalls for the unwary. My attempts to convert to boolean have so far met with nothing but trouble.
    My primary use of IF() is simply to return a zero rather than a #DIV/0! error if precedent cells are not filled. While the boolean version works fine if the precedents are in place I still get #DIV/0! if they’re blank or 0, and I haven’t been able to work out how overcome the problem. For instance, how could this be converted to boolean?
    =IF(OR(C10=0,B10=0),0,
    ((B10-NDEX(B$1:B10,Last(1,B$1:B10),0))
    /SUM(INDEX(D$1:D10,Last(1,B$1:B10)+1,0):D10))*0.625)
    Or this?
    =IF($B10=0,0,(B10-INDEX(B$1:B10,Last(4,B$1:B10)))*0.625
    /SUM(INDEX(D$1:D10,Last(4,B$1:B10)+1,0):D10))
    In both of the above, “Last” is a UDF.
    The problem is that, regardless of whether the boolean argument is false, the rest of the formula goes ahead and calculates and the cell ends up with the dreaded #DIV/0!. In other words, FALSE(0) * #DIV/0! is still #DIV/0!
    Sorry if this is a bit elementary, but it’s not to me!
    Thanks,
    Richard

  121. Thanks for pointing me to the IFERROR function. I’d used the If(Iserror( combination many times in Excel 2003, so I’m glad to see there’s an alternative.

  122. EHA 5 I was here. I promise to stay away from IF unless I absolutely have to! Nearly gone blind in the past trying to parse my way through a thicket of nests!

  123. EHA5 student checking in. I’ve read this one before, and eversince been trying to avoid IF. Boolean logic became a concept fot me now, something I am using in everyday life, even when i am not using Excel. That’s the fun part of it: boolean logic is SIMPLE (1/0). Christal clear post Daniel, cam’t wait to really start understanding and applying the third method you mentioned.

  124. EHA5 Student, a little bit behind but catching up! This new approach makes a lot of sense. It will be good trying not to use IF in a lot of formulas.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>