Does Excel check both result arguments provided by the IF function?

Question

The Excel if function takes three arguments, a condition, an if-true value, and an if-false value. Does Excel distinguish the meaning of all three arguments, or does it only determine the value of the condition and the corresponding result?

Clarification: I am not interested in what the result of if will be; I wonder if it calculates the value of all arguments before calculating the result of the function.

This is equivalent to specifying whether the if function uses a lazy or strict evaluation. For example, the following pseudo-code:

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

will throw a "div-by-zero" exception in a fully rigorous evaluation language, since it will evaluate 1/0 , although the result is not required for the ?: operator.

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

The problem is that in Excel 1/0 creates a legal value (which happens to be #DIV/0! ) That can exist in expressions. So just calling =if(true,1,1/0) doesn't show if Excel 1/0 or not.

Answer 1

Very eastern test

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

I assume that you really mean iif () - in VBA it is not a "short circuit", so you should use If..Then..Else..End If in cases where this can 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 cell: =IF(TRUE,truepart(),falsepart())

Get only one msgbox to calculate IF () cell.

As an additional check, this gives you two msgbox - one for each:

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

Answer 2

A few simple tests:

=IF(TRUE,1,1/0)

=IF(FALSE,1/0,1)

None of them lead to a div / 0 error, so we can conclude that the corresponding result of the condition is actually satisfied. Obviously, the condition itself must be evaluated.

In more complex formulas, you can also use the Evaluate Formula tool to see how IF statements are parsed.

Answer 3

This is not true.

Excel 2013 only evaluates the required code.

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

Thus, using the If operator to avoid calculation when other criteria were proposed, it should be equal to zero and perform the calculation only when necessary, speed up the process, and reduce the processing time to about 10% from the previous one.

If Excel evaluated both expressions, an If statement would only add complexity and time.

Answer 4

Excel seems to use an impatient estimate for the IF() function (that is, it always evaluates all three arguments). To test without VBA, enable the automatic layout of the book and enter the new cell of the book:

 =IF(TRUE, 0, RAND()) 

Save and close the book. Open the workbook again and close it again, and Excel will display a “save changes” prompt because the volatile RAND() function has been evaluated.

Change RAND() to 1 and Excel will not display a prompt.

Answer 5

I have the opposite answer to Tim based instead on XL 2010 If Function:

 =IF(TRUE,1,1/0) 

does not give error # DIV / 0,

 =IF(FALSE,1,1/0) 

does. In addition, my reading implies that only the relevant condition is evaluated.