Does Excel evaluate both result arguments supplied to the IF function?

Question

Excel's if function takes three arguments, a condition, an if-true value, and an if-false value. Does Excel work out the value of all three arguments, or does it only work out the value of the condition and the corresponding result?

Clarification: I'm not wondering what the result of the if will be, I'm wondering whether or not it calculates the value of all arguments before calculating the result of the function.

This is equivalent to asking whether or not the if function uses lazy or strict evaluation. For example, the following pseudocode:

x = 5;
print x>2 ? "Bigger" : "Smaller" + 1/0

would throw a divide-by-zero exception in a language with fully strict evaluation, as it would evaluate the 1/0, even though the result wouldn't be required for the ?: operator.

In a lazy-evaluation language, the ?: operator would evaluate x>2 before even deciding which expression to evaluate.

The problem is that in Excel, 1/0 produces a legitimate value (which happens to be #DIV/0!) that can exist in expressions. Therefore, simply calling =if(true,1,1/0) doesn't show whether Excel is evaluating the 1/0 or not.

Answer 1

Very east to test

? iif(true, 1, 1/0) 'run-time error: division by zero

I'm assuming you really mean iif() - in VBA this does not "short-circuit", so you should use If..Then..Else..End If in cases where that could be a problem.

Ok - testing what you really asked:

'In a VBA module
Function TruePart()
      MsgBox "True part"
      TruePart = "True"
End Function


Function FalsePart()
      MsgBox "False part"
      FalsePart = "False"
End Function

In a cell: =IF(TRUE,truepart(),falsepart())

Only get one msgbox per calculation of the IF() cell.

As further validation, this gives you two msgbox - one for each:

Sub Tester()
    Debug.Print IIf(True, TruePart(), FalsePart())
End Sub 

Answer 2

It does not.

Excel 2013 only evaluates the necessary code.

I had a very complex and time consuming cell formula to copy through a couple hundred thousand rows. It would take a few hours to calculate. But fortunately, it was easy to determine based on some other criteria when the result would be zero.

So using an If Statement to avoid the calculation when other criteria suggested it must be zero, and performing the calculation only when necessary sped up the process immensely, cutting processing time to about 10% of the previous.

If Excel were evaluating both expressions, the If Statement would only have added complexity and time.