AND short circuit?

[yushang]

Hi guys,

I'm not sure if AND support short circuit, for example, when evaluate the first expression we know that the whole expression will be false, so, there is no need to evaluate the second expression. I'm not sure if Excel works this way. Any suggestion? Many thanks!

=AND(1=2,1=1)

[#2]

Yes. If anyone of logical expression is false means, the result will be false.

[#3]

@yushang 

Use the formula evaluation. 

In your example the first part is evaluated and because it is FALSE the second part will not be evaluated.

[#4 · Accepted solution]

@yushang  wrote:  ``no need to evaluate the second expression. I'm not sure if Excel works this way. Any suggestion?``

You are correct:  the AND function evalulates all of its parameters first, then it determines to return FALSE because at least one parameter is false.  See the example below.

If you want left-to-right evaluation only as needed, implement nested-IF expressions.  For example:

IF(condition1, IF(condition2, "both true", "second false"), "first false")

Note:  Do not implement an IFS expression.  Like AND, IFS evaluates all of its parameters first, then it determines the first condition that is TRUE left-to-right.

-----

To demonstrate, insert a new VBA module and enter the following functions:

Function myudf1(x)

myudf1 = x
MsgBox "myudf1"
End Function

Function myudf2(x)
myudf2 = x
MsgBox "myudf2"
End Function

Then in Excel, enter 2 into B1 and 2 in C1, and enter the following formula into A1:

=AND(myUDF1(1)=B1, myUDF2(2)=C1)

Note that we see both MsgBoxes for "myudf1" and "myudf2", even though AND will return FALSE because the first condition is FALSE.

-----

Again with 2 in B1 and 2 in C1, enter the following formula in A1:

=IFS(myUDF1(2)=B1, 1, myUDF2(1)=C1, 2, TRUE, 3)

Note that we see both MsbBoxes for "myudf1" and "myudf2", then it returns 1 because the first condition is true.

Disclaimer:  I cannot test IFS with VBA UDFs.  IIRC, I asked someone to run the test above, and they told me those results.  LMK if your results are different.

[#5]

So, IF is the only Excel function that has the same semantic of "if" in most programming languages? I even doubt whether Excel IF is really a function.

[#6]

@yushang 

IF(...) is really a function in Excel. It is roughly equivalent to

Function MyIF(condition As Boolean, value_if_true, Optional value_if_false = False)
    If condition Then
        MyIF = value_if_true
    Else
        MyIF = value_if_false
    End If
End Function

Another function that will not evaluate all arguments is CHOOSE.

For example, with 1 in B1,

=CHOOSE(B1, myUDF1(10), myUDF2(20))

will only execute myUDF1, and with 2 in B1, it will only execute myUDF2.

With 3 in B1, it will execute neither.

[#7]

Thank you. got it. add CHOOSE to my list.

[#8]

@Joe User
I have a different impression for IFS functions. I agree AND function does not do short circuiting. But I think behaviour of IFS function is a bit different with respect to VBA function call vs excel native function/operation. Here is how I checked using both native operation and your VBA function.
AND function:
Quick and dirty using native operation: If I use a formula "=AND(FALSE, 3/0)" it returns #DIV/0!
VBA function call formula as "=AND(myudf1(1)=2, myudf2(2)=2) both MessageBox appear

IFS function:
Quick and dirty using native operation: If I use a formula "=IFS(TRUE,1,3/0,2)" it returns 1 bypassing evaluation of second condition.
VBA function call formula as =IFS(myudf1(2)=2, 1, myudf2(1)=2, 2, TRUE, 3): both MessageBox appear.
But I don't know the reason for the differences. I might be missing something. Thanks

[#9]

@Joe User 

More exactly, IFS evaluates not ALL conditions, but ones till first TRUE appears and ignores the rest.

[#10]

@yushang  wrote:  ``IF is the only Excel function that has the same semantic of "if" in most programming languages? I even doubt whether Excel IF is really a function``

Again, you are correct.  Excel IF is more like an operator than a function.

-----

@Hans Vogelaar  wrote:   ``IF(...) is really a function in Excel. It is roughly equivalent to

Function MyIF(condition As Boolean, value_if_true, Optional value_if_false = False)``

I disagree.  A true function would evaluate all of "condition", "value_if_true" and "value_if_false" before invoking the function "MyIF".

In contrast, the Excel IF operator evaluates "condition".  Then only if "condition" is true is "value_if_true" evaluated.  And only if "condition" is false is "value_if_false" evaluated.

This is important if the "value_if" parts might call UDFs or if they might be "expensive" (e.g. complicated searches and calculations).

-----

@Hans Vogelaar  wrote:  ``Another function that will not evaluate all arguments is CHOOSE``

I agree.

[#11]

@Sergei Baklan  wrote:  ``IFS evaluates not ALL conditions, but ones till first TRUE appears and ignores the rest``

That is not what I have been told or read.  I provided a method to test the behavior.  Please show us the results.  Unfortunately, I cannot.

EDIT....  No need.  @sanjibdutta  already demonstrates that what I wrote is correct.  I don't know why you ignored it 2 hours later.

PS....  That is also a problem with the new SWITCH function, IIRC.  It evaluates all of its parameters before selecting the result to return.  (TBD)

-----

EDIT....  An important difference with IFS and SWITCH is:  even though all parameters are evaluated (I believe), the function does not through an error like #DIV/0, #VALUE or #NUM unless that parameter is selected.  (TBD)

That much can be demonstrated easily using Excel Online (onedrive.live.com).

[#12]

@sanjibdutta  wrote:  ``If I use a formula "=IFS(TRUE,1,3/0,2)" it returns 1 bypassing evaluation of second condition. VBA function call formula as =IFS(myudf1(2)=2, 1, myudf2(1)=2, 2, TRUE, 3): both MessageBox appear. But I don't know the reason for the differences.``

First, thanks for confirming my assertion about IFS, namely ``both MessageBox appear``.

As for the difference with respect to the #DIV/0 error....  As I noted in a previous response (after yours), even though IFS and SWITCH (et al?) evaluate all parameters before selecting the result, any Excel error is suppressed unless and until it is selected.

FYI, the same is true of a VBA function with 2 or more parameters -- if the parameters are type Variant, and the errant parameter is not referenced (other than IsError).

For example:

Function myudf(cond As Boolean, a As Double, b As Variant) As Double
If IsError(b) Then MsgBox "myudf error" Else MsgBox "myudf"
If cond Then myudf = a Else myudf = b
End Function

In Excel:

=myudf(true, 1, 1/0) returns 1 despite #DIV/0 error in the 3rd parameter

=myudf(false, 1, 1/0) returns #VALUE due the #DIV/0 error in the 3rd parameter

In both case, the MsgBox displays "myudf error", which demonstrates that Excel itself does not recognize the error in the 3rd parameter.

[#13]

@Joe User 

My comment was to this

Like AND, IFS evaluates all of its parameters first, then it determines the first condition that is TRUE left-to-right.

You may compare

=IFS(FALSE, 1, 2=2, 2, 1/0, 3, 3=3, 4)

and

=IFS(FALSE, 1, 1/0, 3, 2=2, 2, 3=3, 4)