LAMBDA example: Cubic Spline Interpolation & Extrapolation

[yake-ho-foong]

I recently wrote about this on my blog, and thought it could be valuable to some folks here:

Link to my blog post 

 The demo file is also available on GitHub and OneDrive; see the links in my post.

[#2]

Hi, thx for sharing. I skimmed through and it looks great. I'm sorry you didn't get the advanced formula environment working but I've been using it for many months and it is great, especially when working with LAMBDA functions.
My only comment I wanted to add is a cautionary comment regarding IFS in particular. As was pointed out to my in a prior post I made, IFS is a FUNCTION and not an OPERATOR. By that I mean there is a big difference between
=IF(A1>1, VeryComplicatedFunction, "no calculation done")
=IFS(A1>1, VeryComplicatedFunction, TRUE, "no calculation done")
because the IF (as long as you don't have arrays being addressed) will act as an OPERATOR and in the above case if A1<=1 it will NOT perform the VeryComplicatedFunction. The IFS on the other hand WILL always perform the VeryComplicatedFunction (i.e. calculate all the arguments) and pass them into the IFS function for evaluation.
This got me because I had a very complicated function that only needed to be done a very small percent of the cases and some easy calculations the other times. I used IFS thinking I was saving processor time to do the simple calculations most of the time but in fact was still doing the complicated formula all of the time PLUS the simple formulas. You can think of calculating speed and including relativity in case you close to speed of light. most of the time you only need newtonian distance over time but once in a while you need to account for relativity. In a case like this you want to use IF() even if that means IF(....IF(...IF(...
NOTE: this does not work if you are doing array calculations as then even the IF() will perform all the argument calculations.

[#3]

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.

1. =LET(X,AVERAGE(SEQUENCE(1000000,100000,0,1)),Y,0,IF(TRUE,Y,X))
2. =IFS(TRUE,0,FALSE,AVERAGE(SEQUENCE(1000000,100000,0,1)))
3. =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. :)

[#4]

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 @Joe User  who helps point some of this out in my post:

https://techcommunity.microsoft.com/t5/excel/ifs-evaluates-all-elements/m-p/3200131

[#5]

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.