Forum Discussion
IFS evaluates all elements
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'
