Does the ordering of the "if true" and "if false" variables in an IF formula change the calculation time?

d0rian

Active Member
Joined
May 30, 2015
Messages
263
Perhaps a dumb Q, but I'm trying to troubleshoot an annoying performance lag, and have a few thousands IF formulas in the standard syntax:
=IF(logical_test,[value_if_true],[value_if_false]

Alternatively, sometimes I'll use the IFERROR function:
=IFERROR(value,value_if_error)

My question is about how Excel goes about evaluating these formulas, and whether it does so sequentially...and if so, whether changing the order of the variables would significantly speed my sheet up. For example:

  • Consider a long/complicated formula that depends on the value of cell A1 which can be either a numerical value, or the letter "x" >> when A1 is a number, the formula returns a value, but when it's "x", it returns an "#N/A" error
    • If I use: =IFERROR([long/complicated_formula],""), Excel essentially has to evaluate the entire [long/complicated_formula] before deciding whether to return its result or a null "" value, right? But what if instead I used:
    • =IF(A1="x","",[long/complicated-formula]) In this instance, Excel's first checking whether A1="x", and if it is, then it doesn't even need to bother evaluating the long/complicated formula, right? This formulation would seem to cut down on calculation time dramatically, but does Excel evaluate IF formula variables in the order they appear and just cut off any calculation once it 'knows' which true/false variable to return? Or will my 'long/complicated' formula need to be evaluated regardless?

Hope that made sense. BTW, though I used an IFERROR for my example above, this also has consequences for how I order the if_true and if_false variables in standard IF formulas...TLDR: is it best practice to always structure them so that more complex formulas nested within them only get evaluated/calculated if they need to be? Or is this moot / do the formulas get calculated regardless?
 
Last edited:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,686
The calculation logic - as far as I know it - only evaluates IF-formulas 'till it finds TRUE ignoring the rest. The evaluation order makes a difference.

IFERROR evaluates the whole calculation and only returns the alternative value if the evaluated formula returns an error.

Often times (but not always) you can replace a complicated IF-structure with a lot simpler lookup-table.
 

d0rian

Active Member
Joined
May 30, 2015
Messages
263
Bumping just to see if anyone else has any opinions / insight into this (though I appreciate your reply, Misca!) P.s. did a little googling, and found a page (though I don't know how authoritative it is) that seems to agree with what you wrote: "...[FONT=&quot]Excel nested If [/FONT]tests conditions in the order they appear in the formula[FONT=&quot], and as soon as any condition is met, the subsequent conditions are not evaluated."[/FONT]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,683
Office Version
  1. 365
Platform
  1. Windows
I agree with Misca and the page that you have referenced.

Note, do not overlook something very important misca said:
Often times (but not always) you can replace a complicated IF-structure with a lot simpler lookup-table.
To add to that, you might be able to replace some of your IF functions with other functions as well.

Of course, without seeing some of these IF formula, we really cannot recommend exactly how you might do that.
But here is one example. This formula:
Code:
=IF(A2="a",10,0)
could actually be replaced like this:
Code:
=(A2="a")*10
 

d0rian

Active Member
Joined
May 30, 2015
Messages
263

ADVERTISEMENT

I agree with Misca and the page that you have referenced.

Note, do not overlook something very important misca said:

To add to that, you might be able to replace some of your IF functions with other functions as well.

Of course, without seeing some of these IF formula, we really cannot recommend exactly how you might do that.
But here is one example. This formula:
Code:
=IF(A2="a",10,0)
could actually be replaced like this:
Code:
=(A2="a")*10

Thanks -- If by 'lookup table', you mean simple VLOOKUP formulas, then yes I've used those...though I usually opt for INDEX(array,row,column) because it's faster and more flexible. (Though I'm not quite sure how/why they'd be used to replace an IF function...they'd seem to do rather different things(?) Though I don't want to bloat this thread with my specific formulas, so don't worry about it for now.)

Can you describe what that final formula is doing? :
Code:
=(A2="a")*10
[/QUOTE]

Not sure I've seen this syntax before....doesn't A2="a" just return TRUE/FALSE depending on whether A2's value is "a"? If so...what does multiplying by 10 do...?
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,683
Office Version
  1. 365
Platform
  1. Windows
I'm not quite sure how/why they'd be used to replace an IF function
You haven't posted your IF formulas, so we have no idea what they may look like. But if you had a nested IF formula where you were checking the same thing for different values, i.e.
Code:
=IF(A2=1,...,IF(A2=2,...,IF(A2=3,... etc
using a lookup or index/match formula may be more efficient.

Not sure I've seen this syntax before....doesn't A2="a" just return TRUE/FALSE depending on whether A2's value is "a"? If so...what does multiplying by 10 do...?
Boolean values (TRUE/FALSE) also have numeric values, specifically 1 for TRUE and 0 for FALSE.
So, you may be able to use that to eliminate certain IF functions, like in the example where I wanted to return a 10 if cell A2 = "a" and a 0 if it does not.

The whole point is that you might be able to replace some of your current IF functions with other functions, which may help performance.
 

d0rian

Active Member
Joined
May 30, 2015
Messages
263
You haven't posted your IF formulas, so we have no idea what they may look like. But if you had a nested IF formula where you were checking the same thing for different values, i.e.
Code:
=IF(A2=1,...,IF(A2=2,...,IF(A2=3,... etc
using a lookup or index/match formula may be more efficient.


Boolean values (TRUE/FALSE) also have numeric values, specifically 1 for TRUE and 0 for FALSE.
So, you may be able to use that to eliminate certain IF functions, like in the example where I wanted to return a 10 if cell A2 = "a" and a 0 if it does not.

The whole point is that you might be able to replace some of your current IF functions with other functions, which may help performance.

Thanks. Is there some resource that shows the overall speed of most common Excel functions? I remember reading somewhere that INDEX/MATCH was substantially faster than VLOOKUP, which I why I always use the former now...(that, and added flexibility.)

But beyond that, I have no idea which Excel functions are, perhaps, notoriously slow/cumbersome...is there some general resource that discusses this that you know of?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,745
Messages
5,626,624
Members
416,195
Latest member
tonmcg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top