In Excel, is there an early opt out AND/OR functions (Short Circuit Evaluation)?

Question

In Excel, is there an early opt out AND function (also known as short-circuit evaluation)?

For example:

=AND(FALSE, #N/A)

Returns #N/A. If the function was an "early opt out", it would return FALSE as soon as the first FALSE was found, as no additional value could make the function ever return true.

Does such a function exist in excel?

Answer 1

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 IFs; evaluation of IFs 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")

Answer 2

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.