IFS evaluates all elements

Silver Contributor

So I like the IFS statement to avoid nested IF() with all those ((())).  But I just ran into a problem using IFS inside a recursive Lambda function.  Basically it appears the IFS statement evaluates ALL its terms before evaluating the IF part which means a MyFunc=LAMBDA(in, IFS(0,MyFunc(in), 1, in))  failed because it recursively evaluated that Lambda function until it hits the limit even though it should NEVER even look at that part of the IFS.  Anyone else find this issue?  Or am I just missing something?  It seems like a very inefficient problem with IFS in general to calculate parts that don't need to be calculated.

5 Replies

@mtarler  wrote: ``it appears the IFS statement evaluates ALL its terms before evaluating the IF part [....] am I just missing something? It seems like a very inefficient problem``

 

 

You are not missing something; not at all.  And as you point out, the greater problem is the potentially incorrect evaluation caused by evaluating parts that should be avoided because the condition is false.

 

In other words, it behaves like a normal function, with one exception:  if an evaluated part results in an Excel error (#VALUE, #NUM, etc), that does not cause the IFS function to fail unless the condition for that part is true -- unlike the evaluation of bona fide functions.

 

Technically, there is nothing wrong with that, except that....

 

In contrast, the IF "function" is really an operator whose syntax has the form of a function.  And we have come to expect that for Excel conditional "functions"; for example, CHOOSE.

 

I noticed that as soon as I learned about the IFS function.  And that is why I deprecate its use.

 

In form, it is merely a shorthand for nested IF's.  There is no other benefit of IFS.  And as you discovered, there are some disadvantages; mostly performance-related, but you discovered a correctness issue as well.

You gave the example of CHOOSE as being like IF as an 'operator' in the form of a function, but I just tested that out and CHOOSE appears to be calculating unused parameters also.
OK let me take that back but add another factor to be considered. CHOOSE(1, ...) does act like an operator as noted above. BUT if you use an array as the operator it will then calculate all parameters and this is true for BOTH CHOOSE and IF. For example
IF( {1,1}, "easy", [some really complicated formula] )
Even though it should just output "easy" "easy" it fully calculates that complicated formula. So the advantage of the 'operator' is lost and it acts as a 'function'

@mtarler 

I noticed this same problem. Thank you for articulating it. I was very happy to have learned about the IFS statement, since it cleaned up formulas quite a bit, but when writing a sudoku solver LAMBDA that recursively looks for new logical values to fill in each recursion loop, I found that I was forced to nest my IF statements, or the program froze (endless looping I presume). I would love an IFS that ceased to check subsequent conditions after the first satisfied condition was found. 

 

I thought about how I would design a BetterIFS recursive lambda function that moved on to the next condition if one wasn't met, but this would require passing all the conditions back to the function... thus evaluating their validity.  Then I thought perhaps under the IFS hood is a giant nest of IF statements 127 levels deep! (the limit as per the IFS documentation), but such a construction still evaluates all the parameters passed to it:

TimothyCalford_0-1679530226114.png

I think I too will steer clear of the IFS statement to avoid errors and unintended evaluations.

@Timothy Calford  wrote:  ``I thought perhaps under the IFS hood is a giant nest of IF statements 127 levels deep! [...], but such a construction still evaluates all the parameters passed to it: [....] I think I too will steer clear of the IFS statement to avoid errors and unintended evaluations``

 

Good conclusion.  I would use IFS to "clean up" syntax only when the side-effect of evaluating all parameters before selection seems minimal.

 

I don't know anything about LAMBA.  But ostenibly, your LAMBA construction with nested IFs should indeed evaluate left-to-right as-needed.  It would, if the IF construct were outside a LAMBA construct.

 

So, the behavior that you seed is a side-effect of the LAMBA design.  Sad.  But good to know.  People should be wary of the inherent inefficiencies of some of these new constructs.  IMHO, they should be used sparingly, only when they truly are the best solution.