# 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.

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. Alan says:

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. Alan says:

Hi,
Can you extend the boolean example to a non-numeric result?
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. Daniel Ferry says:

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. Alan says:

Thanks Daniel – that’s very cool and I agree, it is much easier to intrerpret and troubleshoot.
Alan.

5. 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.

6. Henry Rios says:

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.

7. Rick Rothstein (MVP - Excel) says:

@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.

8. techcircle says:

Hi, The post was very helpful. I like ur blog very much, its quite different from other blogs. Thanks alot.

9. Daniel Ferry says:

@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

10. I found your site on del.icio.us nowadays and really liked it… I bookmarked it and can be back to test it out some more later..

11. Brilliant stuff thanx

12. techcircle says:

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

13. Thanks so much for this post- I needed to write one like this for my readers, but I think I’ll just end up linking to yours!

14. 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.

15. Daniel Ferry says:

@techcircle-
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

16. techcircle says:

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

17. Daniel Ferry says:

@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

18. Loren says:

Perhaps a bit off topic, but are you from the Bruce Ferry clan? If so, hi, been a long time dude. Loren Kinzel

19. Matthew Ferry says:

Yep Loren – that’s him, 2nd son of Bruce Ferry.
Indeed long time dude! Hope all is well.
Matthew Ferry

20. Finally someone that actually knows what they are talking about – thank you!

21. techcircle says:

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

22. techcircle says:

Hi Daniel,The above formula is not complete. Its not showing the complete formula I have entered.

23. Daniel Ferry says:

@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

24. Daniel Ferry says:

@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

25. Loren Kinzel says:

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

26. So what are you doing now? Buy your cheap Viagra now.

27. Jeff Weir says:

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

28. 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.

29. Pete C says:

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.

30. Cyril Z. says:

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

31. Cyril Z. says:

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
```
32. chrisham says:

Thanks Daniel, simply a great tip!

33. Daniel Ferry says:

@chrisham –
You are most welcome.
Daniel Ferry
excelhero.com

34. https://me.yahoo.com/a/kL9zkykIvJ7G7zQQRLzc5H0dWsBywQ--#b1c9c says:

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?

35. Daniel Ferry says:

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

36. sam says:

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.

37. elmaster84 says:

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

38. https://me.yahoo.com/a/qvsoprByvZmc9xzIcIPhoVilxBBCf5ok2Tt.WIwnTWMlErs-#5a5af says:

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!

39. KimC says:

I had a Eureka moment when ready about the Boolean logic replacement formula. i love the simplicity of it.

40. Hans says:

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.

41. weir.jeff says:

@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’

42. Ulrik Willemoes says:

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!

43. Richard Emptage says:

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.

44. poombs says:

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().

45. tim j says:

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

I shall now make “IF() avoidance” a priority in my spreadsheet designs.

47. music43 says:

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)

48. riverman21 says:

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)

49. transly says:

Great post, thanks, Daniel! -Luke Liu (student of EHA)

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.

51. Peter says:

Thanks Daniel.
Another self-confessed IF addict will now try to go cold-turkey for a while.
Cheers,
Peter

52. dennet says:

Great post, Daniel. My special thanks for the REPT trick in the comments. Definitely, new stuff for me.

53. Jason Cobb says:

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)).

54. Bruce says:

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)

55. Nacho says:

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)

56. Miss says:

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.

57. ramona.lytle says:

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)

58. gwschill says:

Another great Article. Enjoyed the read.
Greg S.

59. Paul S says:

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″

60. Paul S says:

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)

61. Glenn Brooks says:

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.

62. https://me.yahoo.com/a/297GHrI.rPSKOmEZd_cjzdTaOyl_ZQ--#bc917 says:

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)

63. dlevine5 says:

Excellent. Thanks, Daniel!

64. Iain Voller says:

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

65. https://me.yahoo.com/a/x5KdIEx.0NmL.oKiUUw6J7yibWSI3Bb6pp2dEHgDI9AEnO4-#b6bfc says:

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
Sorry, I have no idea how to use HTML tags to make the formula viewable.

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)

Great article! Thanks.
-Harold

68. Tim Festerling says:

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

69. Neale says:

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

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

71. ksunberg says:

Great post! The boolean formula is definitely easier to read than nested ifs.
Thanks,
Kristi (EHA student)

72. Antonio says:

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.

73. bwasny says:

“No ifs” or buts about it. This is an “Excel”ent post.

74. https://me.yahoo.com/a/uWFBKbJ.2tSnGOJAGyEPouaQCsQ-#721bd says:

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

75. MarkV says:

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)

76. Patrick Sullivan says:

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.

77. Michael Silberthau says:

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

78. Janice Duffney says:

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

I believe up until this point I have been an over-IFer…

80. andy.jwall says:

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

81. Richard Kehl says:

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.

82. Elaine Emptage says:

Very interesting article.

83. marksnicholson says:

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

84. tomquist says:

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)

85. maggierettenberger says:

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

86. Paula says:

IF has been my go to function. It’s great to learn some alternatives. Thanks Daniel!
Paula

87. Vic says:

Very interesting examples of If alternatives
Thanks for sharing
…On the way of becoming Excel Hero

88. vinu1smile says:

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

89. mms says:

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”

90. andrea.ciaramella says:

Read and understood, I already used the vlookup method but the boolean logic is new to me and seems extremely powerful.
Thanks !
Andrea

91. Rick Rothstein (MVP - Excel) says:

@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”)

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

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

94. Drazen says:

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)

95. Rudy says:

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!

96. Daniel Ferry says:

Rudy,
G12 = REPT(VLOOKUP(“*”&P12&”*”,LookupRng,6,FALSE),OR(D12={“Approved”,”Denied”}))

97. Rudy says:

Daniel,
Could you show a situation where there is no other alternative but to succumb to the IF() curse!

98. marcin.markiewicz says:

Read the text duly. Now off to 5 and 3 is 1
Marcin “Kuty” Markiewicz
EHA2

99. bijoymathew says:

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

100. rcrocker13 says:

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.

101. BobN says:

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

102. ChrisDiGi says:

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

103. ajp says:

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

104. Matthew says:

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)

105. Excelsnail says:

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?

106. randles.dan says:

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

107. lbryanhall says:

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

108. MartinEHA2 says:

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

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

110. MartinEHA2 says:

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

111. Daniel says:

I’m going to have to give this a try.

112. Simon says:

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

113. Rick says:

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.

114. Rick says:

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.

115. https://me.yahoo.com/a/BrC6O3hj35d.LqBRx9tPgZ5bOzIjERc-#5d586 says:

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

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!

117. tim.mccollough says:

EH2.
homework done.
Tim McCollough

118. Michael Rapp says:

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

119. GeoffBeals says:

Have read this several times over the last 6 months but still worth another read for homework for EH2.
GeoffBeals

120. juanito says:

Great article, introducing the incredible versatility of Boolean logic in Excel.
I was here!
– Juanito EHA2

121. eshleman60 says:

This is good information. Thanks Daniel!

122. cptriple says:

I am guilty of using nested IF statements as well. This article will help me simplify formula creation.

123. Bill Wilson says:

This makes sooo much sense. That’s why I love this class!

124. https://me.yahoo.com/a/E7hFftt4sIjLBgS8W_o094oLkp4ygg--#62282 says:

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

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

126. Secant7 says:

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?

127. Rick Rothstein (MVP - Excel) says:

@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)

128. Lorette says:

Happy to get alternatives for all my nested IF functions!
Lorette
EHA2

129. papercut_121 says:

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

130. GreenTor says:

Very good article

131. kdewhitt says:

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

132. john.broggio says:

Very, very useful.
Will be eradicating unnecessary IF’s when I get back to work…
John – EHA2

133. rbertin says:

Hi Daniel!!!
Let´s try to not use “if” at all!!!
Regards,
Rodrigo – EHA2

134. Joseph Burbea says:

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…

135. https://me.yahoo.com/a/6db_0w4isMum1X.KzhHifkrH9SRmWA--#e2d72 says:

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!

136. alessandro.alessandrini says:

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

137. constant.verweij says:

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)

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

139. Brent Patchin says:

Great Article Daniel

140. KenU says:

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

141. SongNYC says:

EHA3_HW
Another Excellent article Daniel.

142. Nimir says:

EHA3_HM
Thank you for sharing Daniel.

143. jullian2001 says:

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.

144. daffy.email says:

EHA3 Homework

145. jon.acampora says:

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

146. John H says:

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

147. chriscorpion786 says:

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

148. vitojarque says:

EHA 3 HW
Done!
Vito Jr

149. dleverett says:

I read this once before, but it makes even more sense now. Great article.
Dan Leverett, EHA3

150. siyingruan says:

Homework done.

151. Stech says:

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

152. John says:

Wow, this makes rethink a lot of the formulas I use.
John
EHA3 – ndarmyserver

153. Guilherme.Brito says:

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

154. PeterH says:

EHA3 homework

155. Beeble says:

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

156. Daniel says:

Great article.

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

Great article. I never thought of using boolean logic like this.. Good stuff!
John EHA3-student

159. dbelluscio says:

Completing my homework for module 5

160. tom.schlosser says:

A paradigm shift in working with excel for me!

161. Unai EHA3 says:

“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)

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

163. Jeff Lenning says:

As always, good stuff Daniel.

164. bspringmeier says:

Great info! Thanks!

165. bspringmeier says:

Great info! Thanks!

166. bspringmeier says:

Great info! Thanks!

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.

168. ChrisG says:

Very useful info. IFERROR() – definately something I will use more of to simplify my formulas.

169. MattW says:

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!

170. Cruiser94 says:

Very interesting! Sounds like I have a lot of work to do to my library of Excel files!

171. Anne says:

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

172. snewbern says:

Thanks, Daniel.

173. lneely says:

Thanks. I hope to move away from IF, though admittedly VLOOKUP is my go to… Leia

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

175. Matthew.Barrowclough says:

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!

176. Matthew.Barrowclough says:

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!

177. mattomer says:

I def fall into the IF() trap a lot at work. I look forward to using boolean logic more.
Thanks!

178. Larry J. says:

Thanks for the article. This opens up so many options for me. Will need to experiment with the formulas.

179. Bermir says:

Bermir was here. Not necessarily an if-lover but probably an if-overuser.

180. Julie says:

Julie was here…

181. Kathy C says:

Definitely guilty of IF() excess

182. Hatteras says:

183. Michelle says:

I have definitely seen the excessive nesting of IF functions….love the boolean alternative. Elegant, as you said.
Michelle

184. Mikhail Samsonov says:

Daniel, thank you for explaining boolean alternatives of the if() function.
Mikhail
EHA4

185. Ray Willoughby says:

186. cory says:

I must admit I do use IF() probably more than i should

187. James says:

Very good article. I really like the boolean option. I never really considered it before. Thanks.

188. RonBonne says:

Use the “IF” a lot, but like the creative use of Boolean logic. Will use that more often now.

189. marith says:

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.

190. https://me.yahoo.com/a/4tOcw4wI1IMzFMLtsjDQUQMdyfBVuaib1Tmjn9xLvwWN#45416 says:

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!

191. goneps says:

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

192. Paul Kjar says:

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.

193. Dennis Z says:

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!

194. Kate EHA5 says:

I love the if statement, but promise to learn to use other things that might be better suited.

195. DavidHoppe says:

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.

196. JimH says:

EHA5 check in – I’ve spent a lot of time using the If statement but look forward to the next level

197. Tammy S says:

What a brilliant idea – I may never use IF again

198. https://me.yahoo.com/a/a0eO1wd0gsfonByi7T.fC1WqfZSPkFg-#5fba9 says:

EHA5 student – move to next level, forget IF.

199. eduardotk says:

Excellent! Finally better ways to do logical tests than using the IF() function. Thanks!
eduardotk
EHA5 student

200. Dan Mayer says:

Just when I was starting to feel good about using IF…

201. John M says:

EHA05
Somewhat behind the curve on lessons but plugging away

202. mattm says:

EHA5 Student – Great lesson, it will be fun trying to not use the IF statement.

203. Ricardo T says:

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.

204. Brian Johnston says:

Very Effective. This will certainly help to speed up some of my larger spreadsheets.