In Excel, is there an early opt out AND/OR functions (Short Circuit Evaluation)?
What you're calling "early opt out" is more commonly called "short-circuit evaluation," and is generally a feature of languages in the C/C++/C#/Java family (but, notably, not Visual BASIC).
For Excel formulas, some logical functions practice short-circuit evaluation but some do not. AND
does not, as you've found. Neither does OR
--if the first argument in an OR
expression is true, Excel will still try to evaluate the subsequent arguments.
The solution is to use nested IF
s; evaluation of IF
s goes step-by-step from outer to inner, branching as necessary, and stopping when there is no further nested formula to be evaluated. This produces the correct short-circuit behavior. So you can write your code above as
=IF(FALSE, IF(ISNA(<address of cell to test for the #N/A state>), TRUE), FALSE)
Another example that may be more clear:
Cell A1 contains the value 1 and cell A2 contains the formula "=1/0",
causing a #DIV/0 error.
Put this formula in A3:
=IF(A1 = 0, IF(A2 = 5, "Never get here"), "Short-circuit OK: A1 <> 0")
The function you're looking for does not exist in native Excel.
You could however, imitate it, e.g. using IFERROR
:
=AND(FALSE,IFERROR(A1,FALSE))
(Work ins 2007 and beyond. In 2003, you need to use =IF(ISERROR(A1),FALSE,A1)
instead of IFERROR(A1,FALSE)
.)
Alternatively, you could build a User Define Function:
Public Function EarlyAnd(var1 As Variant, ParamArray vars() As Variant) As Boolean
On Error GoTo Finalize
Dim blnTemp As Boolean
Dim varNext As Variant
If Not CBool(var1) Then GoTo Finalize
For Each varNext In vars
If Not CBool(varNext) Then GoTo Finalize
Next
blnTemp = True
Finalize:
EarlyAnd = blnTemp
End Function
Place this function in a module in the Visual Basic Editor. Now you can use =EarlyAnd(False,A1)
in your Excel.