I Heart IF
– By Daniel Ferry, Excel Hero, MVP
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.
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)
If you liked this article, please share it!
Courtesy: excelhero.com
Pages: 1 2
I’ve attempted—unsuccessfully—to apply your syntax to devise a formula: =IF(logical_test,[VALUE_if_true],[VALUE_if_false]) so: =IF (j2=”T”, [i2*0.0835],[i2]) and alternately, =IF (j2=T, [i2*0.0835],[i2]), but Excel gives me garbage. Your explanation defies practical implementation. It’s been several years since I tried to do this; I’ve forgotten how. I’m trying to devise a spreadsheet that will calculate tax on a T/F basis for a line subtotal.
I’ve also tried omitting the commas, to no avail.
=IF(J2=”T”,I2*0.0835,I2) I do not see any reason why this formula isn’t working. [remove the square brackets]
please let me know if you are having any questions.
Hi there,
Is it possible to create a formula that will return a different calculation based on a given value? I’m trying to make a formula that will let me multiply the value in a cell based on a daily, weekly, or monthly rate. E.g. IF(C2=”Weekly”,B2*4). A single condition works but how do I create a formula that will check for three? Thanks for any help 🙂
try this
IF(C2="Daily",B2*4,IF(C2="Weekly",B2*6,IF(C2="Monthly",B2*6,"Wrong Data")))I want to make a formula as (If,0,0) If not then (F27-G27) what should I put on: ( if not then) on original formula?