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:

=LAMBDA(knowny,knownx,newx,TREND(VSTACK(INDEX(knowny,XMATCH(newx,knownx,-1,2)),INDEX(knowny,XMATCH(newx,knownx,1,2))),VSTACK(INDEX(knownx,XMATCH(newx,knownx,-1,2)),INDEX(knownx,XMATCH(newx,knownx,1,2))),newx))

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.