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

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

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

&REPT(G12,(C6=”(K)”)*(G6>=23)*(D6>=265)*(D6=265)*(D6

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

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 :

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

This is very true Sam.

Especially if you want to have a Criteria in SMALL function

http://crispexcel.com/find-the-last-or-nth-occurence-in-excel/

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

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!

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

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.

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

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!

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.

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

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.

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)

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)

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.

Thanks Daniel.

Another self-confessed IF addict will now try to go cold-turkey for a while.

Cheers,

Peter

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

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

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)

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)

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.

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)

Another great Article. Enjoyed the read.

Greg S.

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″

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)

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.

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)

Excellent. Thanks, Daniel!

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

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.

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

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

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

Great post! The boolean formula is definitely easier to read than nested ifs.

Thanks,

Kristi (EHA student)

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)

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

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

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)

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.

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

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…

Scott Wiltshire – Academy homework

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

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.

Very interesting article.

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

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)

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

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

Paula

Very interesting examples of If alternatives

Thanks for sharing

…On the way of becoming Excel Hero

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

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”

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)

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

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!

Rudy,

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

Daniel,

Thanks for your reply!

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

Read the text duly. Now off to 5 and 3 is 1

Marcin “Kuty” Markiewicz

EHA2

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

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.

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

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

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

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)

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?

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

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

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

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

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

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

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.

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.

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!

EH2.

homework done.

Tim McCollough

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

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

GeoffBeals

Great article, introducing the incredible versatility of Boolean logic in Excel.

I was here!

– Juanito EHA2

This is good information. Thanks Daniel!

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

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

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

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?

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

Happy to get alternatives for all my nested IF functions!

Lorette

EHA2

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

Very good article

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

Very, very useful.

Will be eradicating unnecessary IF’s when I get back to work…

John – EHA2

Hi Daniel!!!

Let´s try to not use “if” at all!!!

Regards,

Rodrigo – EHA2

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…

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!

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

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)

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

Great Article Daniel

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

Ken, EHA3

EHA3_HW

Another Excellent article Daniel.

Your ‘LED RSS News Ticker’ file is INSANE!

EHA3_HM

Thank you for sharing Daniel.

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.

EHA3 Homework

Read this before getting into the Academy! Great article.

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

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

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

EHA 3 HW

Done!

Vito Jr

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

Dan Leverett, EHA3

Homework done.

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

Wow, this makes rethink a lot of the formulas I use.

John

EHA3 – ndarmyserver

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

EHA3 homework

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

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

Completing my homework for module 5

A paradigm shift in working with excel for me!

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

As always, good stuff Daniel.

Great info! Thanks!

Great info! Thanks!

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.

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

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!

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

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

Thanks, Daniel.

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.

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!

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!

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

Thanks!

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

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

Julie was here…

Definitely guilty of IF() excess

Very helpful! thanks! Joe

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

Michelle

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

Mikhail

EHA4

Additional information which reinforces the boolean topics in your EHA class.

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

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

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

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.

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!

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

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.

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!

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

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.

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

What a brilliant idea – I may never use IF again

EHA5 student – move to next level, forget IF.

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

eduardotk

EHA5 student

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

EHA05

Somewhat behind the curve on lessons but plugging away

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

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.

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