The previous post demonstrated that IF and CHOOSE short-circuit but IFS and SWITCH do not.
But following up a hint from Bill Wu that IF behaves differently in array formulas I decided to check whether it still short-circuits.
(IF in array formulas currently only returns a maximum of 255 characters but non-array IF does not have this limitation)
The short answer is that IF and CHOOSE do not short-circuit in array formulas but use a different and very efficient trick. So lets see how they work.
I am using the same VBA Pass UDF as in the previous post but using an array instead of a single value as the first argument to IF.
Option Explicit Function Pass(valu As Variant) Pass = valu Debug.Print valu End Function
The results in the immediate window are slightly surprising:
So what is Excel doing here?
The standard way Excel handles functions is to first evaluate each of the function arguments and only then call the function and pass it the results of the argument evaluations.
When the IF is an array function instead of doing short-circuiting it works in the standard non-short-circuiting way:
- B5:B8 evaluates to an array containing False, False, True, False
- Pass(80) evaluates to 80 and puts 80 in the Immediate window.
- Pass(90) evaluates to 90 and puts 90 in the Immediate window
- Then the IF function is called with these values.
Excel knows that it has to return 4 values so creates a 4 cell output array and then calls the IF function 4 times passing it a single value from the True/False array and the 80 and 90 result of evaluating the Pass functions. Excel puts the results of the 4 IF calls into the output array and finally returns the array to the grid. This array process is called “Lifting”
This lifting process is very efficient because the 2 Pass() arguments only get evaluated once rather than 4 times.
What about CHOOSE, IFS and SWITCH?
The same lifting process happens for array formulas of CHOOSE, IFS and SWITCH (and indeed all other functions that are not natively array capable).
255 character limit in Lifting?
I wonder if the reason for the 255 character limit is that Excel has to create the output array before calling the function so does not know what the maximum return size is, and for efficiency reasons creates fixed length array items which then get appropriately trimmed when returning to the grid.
This is wonderful. I asked exactly this question on the Excel forums awhile ago and had limited insight in the replies. Really glad to see you dig into this.
The 255 character string limit might be an artifact of using counted multi-byte character strings internally. Excel does not always evaluate arguments before passing to them to functions.
I just found this site and it appears to be the exact type of site I’ve been looking for. Have you compared any performance metrics between spill ranges vs. pivot tables? Either way this is great, keep up the good content.