In Excel, is there anyway to abandon the AND / OR (short circuit assessment) functions?

Question

Does Excel have a failure function and a function (also known as short circuit assessment) before?

For instance:

=AND(FALSE, #N/A) 

Returns # N / A. If the function was “before failure”, it will return FALSE as soon as the first FALSE is found, since no additional value can cause the function to ever return true.

Is there such a function in excel?

Answer 1

What you call “early failure” is more often referred to as “short circuit assessment” and is typically a sign of languages ​​in the C / C ++ / C # / Java family (but not Visual BASIC in particular).

For Excel formulas, some logic functions use a short circuit assessment, and some do not. AND no, as you have already found. Also OR - if the first argument in the OR expression is true, Excel will still try to evaluate the subsequent arguments.

The solution is to use nested IF s; IF evaluation goes step by step from external to internal, branches out as necessary and stops when there is no additional nested formula for evaluation. This creates 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 are looking for does not exist in native Excel.

However, you could imitate it, for example. using IFERROR :

 =AND(FALSE,IFERROR(A1,FALSE)) 

(Work in 2007 and beyond. In 2003, you need to use =IF(ISERROR(A1),FALSE,A1) instead of IFERROR(A1,FALSE) .)

Alternatively, you can create 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 the module in the Visual Basic Editor. Now you can use =EarlyAnd(False,A1) in your Excel.