Linear interpolation helps

Linear interpolation is a somewhat common question that comes up on the forums, and I see a few different ways people approach it. With my recent "upgrade" to the 2024 version of Excel, I decided to make my first LAMBDA() UDF attempt to be for linear interpolation. Then I decided to post it here as a possible help for others. I don't want to claim that my approach here is the only or the best approach, so, if anyone has their own offering to make, please feel free to add it.

The LAMBDA() function I came up with is this:

Please Login or Register  to view this content.

I structured the function call to be basically the same as the TREND() function and Gnumeric's INTERPOLATION() function. Simply assign the function to a name in the name manager, and you have a UDF that you can call that will do linear interpolation.

One note. As currently programmed, the function assumes that your "known_x" values are sorted in ascending order (see the optional toggles in the XMATCH() functions). It also returns an error if your newx value is outside of the range of knownx values (it only does interpolation, it won't do extrapolation).

Usage is illustrated in the attached file. I've also included the old strategy for interpolation that I have recommended over the years.

Hopefully it is helpful for others. Feel free to add your strategies for linear interpolation to give users different options.

Thanks for sharing.

A couple of days later, and I notice something else about this. For those who have enthusiastically embraced "spill" formulas, this interpolation function doesn't spill correctly. If you give it multiple newx values, it will not perform the interpolation correctly. It will return multiple results, but the results won't be correct. The old style "program one cell for one result then copy/paste to get multiple results" that has dominated spreadsheet programming for 40+ years is so engrained in me that I didn't think to worry about how the function would behave under "program one cell to get multiple results" paradigm. I don't immediately see a way to make this function spillable, but I do note that the "old strategy" readily spills =VLOOKUP(P8:P15,$K$7:$N$19,4,TRUE)+VLOOKUP(P8:P15,$K$7:$N$19,3,TRUE)*P8:P15. Maybe something that doesn't use the TREND() function to perform the final interpolation would work.

Here's a lambda function that will spill correctly

Please Login or Register  to view this content.

The TREND() function was the reason it wouldn't spill correctly, so I ended up completely eliminating the TREND() function from the formula. The "downside" is that this version requires 7 lookups, where the prior version only required 4 lookups. They are binary search lookups, so they should be fast, but there is more duplicated lookup effort in this version than the post #1 version. There are also a lot more parentheses in this one, which makes it harder to keep track of which parentheses go where.

Here's a version that will spill correctly, and I figured out how to put the calculation inside of a LET() function so that the lookup step only occurs once (in theory, anyway):

Please Login or Register  to view this content.