Forum Discussion
LAMBDA example: Cubic Spline Interpolation & Extrapolation
Hi mtarler, thank you for pointing this out. It did cross my mind but I did not take the time to test it out. Unfortunately, this issue (of no short-circuit branching) affects LET as well, even when no volatile functions are used.
(1) and (2) below will each give an out-of-memory message, while (3) works wtihout any issue.
- =LET(X,AVERAGE(SEQUENCE(1000000,100000,0,1)),Y,0,IF(TRUE,Y,X))
- =IFS(TRUE,0,FALSE,AVERAGE(SEQUENCE(1000000,100000,0,1)))
- =IF(TRUE,0,AVERAGE(SEQUENCE(1000000,100000,0,1)))
Hopefully, Microsoft will fix this in the future.
I have updated my blog post on this point and credited you for bringing it up. 🙂
I don't think the problem in #1 above is with the IF statement, it fails before it gets there. So
=LET(X,1,Y,0,IF(TRUE,Y,AVERAGE(SEQUENCE(1000000,100000,0,1))))
works fine. [edit] oh I see your point is that even unused variables in a LET statement are still calculated. maybe your example could/should be:
=LET(X, AVERAGE(SEQUENCE(1000000,100000,0,1)), Y, 0, Y)
to really emphasize how X isn't being used but will still be calculated. But this is true of all the new array functions.
The problem with arrays inside the IF are more interesting in that the IF operator no longer acts as an operator but as a function and calculates and passes all the parts. For example:
=IF({TRUE,TRUE},0,AVERAGE(SEQUENCE(1000000,100000,0,1)))
Instead of 0, 0 you get the error.
This is also true for CHOOSE() and other similar functions.
As for credit I would like to call out JoeUser2004 who helps point some of this out in my post:
https://techcommunity.microsoft.com/t5/excel/ifs-evaluates-all-elements/m-p/3200131
It seems that this eager-vs-lazy evaluation issue affects LAMBDA as well, not just LET. For example, Excel does not "get" that the parameter x is not used:
=LAMBDA(x,y,y+1)(AVERAGE(SEQUENCE(100000,100000,0,1)),2,3)
I've encountered something similar with VBA UDF in the past, where I marked my UDF as non-volatile, but Excel still made unnecessary (re-)calculations.
I may be wrong, but I do not see why it could be difficult for Excel to detect which parts do not depend on any volatile functions and hence can be lazily evaluated. Do you know where we can give a shout to Microsoft for future feature enhancements? Thank you.EDIT: A slight correction to my statements above. In order for lazy evaluation to work, the expression (to be skipped in case it is not used) needs to be purely functional, i.e., not only non-volatile but also to have no side effects, e.g., no writing to file in a VBA UDF. LAMBDA functions have access to the global namespace so this might make this issue slightly more complicated, but I still think it should not be difficult for Excel to track which expressions are purely functional.