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

[d0rian]

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?

 

[Misca]

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]

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]

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:

=IF(A2="a",10,0)

could actually be replaced like this:

=(A2="a")*10

 

[d0rian]

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:

=IF(A2="a",10,0)

could actually be replaced like this:

=(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? :

=(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...?

 

[Joe4]

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.

=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]

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.

=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?

 

[MARK858]

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?

See what you can find in the link below...

http://www.decisionmodels.com/optspeede.htm