Excel IFS short circuit evaluation

Question

I've discovered something a little disturbing about the IFS() function.

The normal IF() function does short-circuit evaluation:

=IF(condition, expression1, expression2)

If condition is true, only expression1 is evaluated; if condition is false, only expression2 is evaluated.

Now, I rewrite this using IFS():

=IFS(condition, expression1, TRUE, expression2)

Regardless of the state of condition, both expression1 and expression2 are evaluated. I discovered this while putting some guard conditions on a recursive LAMBDA() function to prevent runaway evaluation. Even with the guard conditions, Excel would lock up for 20+ seconds, leading me to believe that it was evaluating the IFS() function in its entirety. Taking IFS() out completely and sticking with the final recursive call led to the function taking the same amount of time. Rewriting the guard conditions to use nested IF() calls eliminates the problem.

This can be demonstrated with the following LAMBDA() functions, added to Name Manager:

Recursive1: =LAMBDA(count, IF(count = 0, 0, count + Recursive1(count - 1)))

Recursive2: =LAMBDA(count, IFS(count = 0, 0, TRUE, count + Recursive2(count - 1)))

This is what my workbook looks like:

	A	B	C
1	Count	=Recursive1(A2)	=Recursive2(A2)
2	0	0	#NUM!

The #NUM! value is returned when there's a stack overflow in the recursive call; C2 gets there no matter what, and B2 gets there when A2 is above 5,460.

As there is no documentation one way or another regarding how IFS() evaluates its arguments, I hesitate to call this a bug, but with LAMBDA() functions now a core part of Excel, in my opinion it should work as follows:

for index = 1 to count(expressions) increment by 2

if evaluate(expressions(index)) then

return evaluate(expressions(index + 1))

return #N/A

There are three good reasons to handle it this way:

1. It would be consistent with the way the IF() function works.
2. The IFS() function is a logical choice for input validation in a LAMBDA() function, especially one that is potentially recursive, and going through the evaluation of all expressions can lead to unpleasant performance problems.
3. The IFS() function can handle up to 127 conditions, so even without LAMBDA() recursion it's an unnecessary load; large tables with complex formulas could potentially benefit from this optimization as well.

Answer 1

You are correct as to how it works (though I would note that it evaluates every argument passed: both conditions and expressions) - see Charles' blog here: https://fastexcel.wordpress.com/2023/01/03/short-circuiting-excel-formulas-if-choose-ifs-and-switch/

Answer 2

I found a temporary solution, at least for my use case. Many of my instances of IFS() are input validation, with the complex calculation reserved until the end:

=IFS(condition1, error1, condition2, error2, condition3, error3, TRUE, expression)

Because of the lack of short circuiting, the expression gets evaluated even if one of the previous conditions is true.

However, if there is no matching condition, IFS() returns #N/A. Using the same VBA UDF trick as in the Fast Excel blog, I determined that IFNA() does short circuit evaluation, so now I can rewrite my formula as follows:

=IFNA(IFS(condition1, error1, condition2, error2, condition3, error3), expression)

As long as none of my errorN expressions return #N/A, I get a more performant (but not ideally so) LAMBDA() function, as the expression doesn't get evaluated unless all the conditions are false.

Answer 3

Ugh. That's horrifying.

I've submitted feedback to Microsoft on this. Let's bring short circuit evaluation to Excel!

https://feedbackportal.microsoft.com/feedback/idea/73da0f38-33d2-ed11-a81b-6045bd808412

Answer 4

Upvoted! :)

Answer 5

Dear Kevin Dean - Dolphin,

Thank you for posting to Microsoft Community. We are happy to assist you.

We are looking into your situation, we will update the thread shortly.

Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely,