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)
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.
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.
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
Thanks Daniel - that's very cool and I agree, it is much easier to intrerpret and troubleshoot.
Alan.
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.
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.
@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.
I'm buying you a beer if you ever come to Tampa.
Hi,
Hi, The post was very helpful. I like ur blog very much, its quite different from other blogs. Thanks alot.
@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
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..
Brilliant stuff thanx :)
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
Thankyou in advance.
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!
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.
@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
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
Thank you in advance
@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
Perhaps a bit off topic, but are you from the Bruce Ferry clan? If so, hi, been a long time dude. Loren Kinzel
Yep Loren - that's him, 2nd son of Bruce Ferry.
Indeed long time dude! Hope all is well.
Matthew Ferry
Finally someone that actually knows what they are talking about - thank you!
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(D6
After:
=REPT("ITOR",(C6="(K)")*(D6 &REPT("ITOR",(C6="(K)")*(D6>298))
&REPT(G12,(C6="(K)")*(G6>=23)*(D6>=265)*(D6=265)*(D6
Is there any room for improvemet??
Regards,
Rupesh
Hi Daniel,The above formula is not complete. Its not showing the complete formula I have entered.
@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
@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
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
Hiya, nice day.. Your article is extremely impressive. I never considered that it was feasible to accomplish something like that until after I looked over your post. You certainly gave a great perception on exactly how this whole process works. I will make sure to return for more advice. Thanks
So what are you doing now? Buy your cheap Viagra now.
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.
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.
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.
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 :
The results are :
Regards.
Cyril
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 IFThanks Daniel, simply a great tip!
@chrisham -
You are most welcome.
Daniel Ferry
excelhero.com
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
Hello.
I think you want IFERROR (not iSerror). You can indeed roll your own for previous versions. Here is the VBA:
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
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.