Optimization- how does Excel evaluate IF statements?

[cotoews]

Hi all- I have a worksheet (Sheet A) with several thousand rows and on each row, I have twelve monthly columns, each of which performs a cross-sheet sumproduct from data on sheet B. (Long story short, the sumproducts are splitting/spreading some value from sheet B.) The vast majority of these sumproducts will resolve to 0, and if there is nothing to spread for that line item, there's no reason to make Excel calc the sumproduct twelve times; I knew each of the twelve columns will be 0. I'm wondering if I can use a helper column to perform some logic to determine if the sumproduct is even needed (which would return a TRUE/FALSE), and then append an if statement in front of my sumproducts to look at my helper column. Essentially, I'd be reducing my sumproduct calcs for the 0 lines from twelve (for the monthly columns) to one (for the helper column).

The question boils down to: if the condition in an IF statement resolves to TRUE, does Excel completely ignore the [value if false] argument from a calculation perspective? Or are both [value if true] and [value if false] arguments calculated, and Excel will only return the relevant response based on the [logical test]?

 

[joeu2004]

The question boils down to: if the condition in an IF statement resolves to TRUE, does Excel completely ignore the [value if false] argument from a calculation perspective?

Yes.

 

[shg]

Which you can see if you watch the formula evaluate.

 

[BenMiller]

Which you can see if you watch the formula evaluate.

Which you can do if you go to the FORMULAS tab on top of your screen, then click EVALUATE FORMULA.

 

[joeu2004]

The Evaluate Formula feature can be misleading, so it is not dispositive. A trivial example:

=IF(TRUE, 1, 1/0)

Click Evaluate, and all we see is the result: 1.

Although the fact that we do not see a #DIV/0 error might suggest that the false-part is not evaluated, we can only conclude that if Excel evaluated the false-part, it ignored the result, having later discovered that it was not needed.

IMHO, a better way to demonstrate the left-to-right nature of IF() is a formula of the form:

=IF(TRUE, myUDF(1), myUDF(1/0))

where myUDF can be simply:

Function myUDF(x)
If IsError(x) Then MsgBox "myudf x=error" Else MsgBox "myudf " & x
End Function

With TRUE, we see only "myudf 1". The false-part is not evaluated.

Change to FALSE, and we see only "myudf x=error". The true-part is not evaluated. (EF shows #N/A.)