Two Dimensional Linear Interpolation

**rabbitfoot345** · 08-25-2018, 12:32 PM

Hi All Along the x axis I have say 20 30 40 and on the y axis have in descending order 500, 400,300,200,100,0,-100. The scores are in the middle. Basically, higher scores to the upper right, lower scores to the lower left.

Given say an x value of 27, and a y value of 237, what two dimensional interpolation formula could I deploy to find the "score" ? A formula (in know "forecast" in in there) has to interpolate between 20 and 30, then 300 and 200 and then interpolate again to find the final answer.

Thanks if have any suggestions.

**MrShorty** · 08-25-2018, 12:42 PM

A basic outline I put together for bilinear interpolation (assuming bilinear interpolation is an accurate description of your desired algoirthm): https://www.excelforum.com/excel-gen...ml#post4300787 I note that it seems that the most difficult part of linear interpolation algorithms seems to be the lookup step, so pay particular attention to the lookup step.
Here's one that implements a trilinear interpolation. Scaling back to bilinear should be straightforward: https://www.excelforum.com/excel-for...rpolation.html
Note that in both cases we are proposing multi-cell solutions. I don't know about trying to compress a multivariable linear interpolation algorithm down to a single cell function.
I don't know if any still do, but my old copy of QuattroPro has a built in @LINTERP() function that would make this a lot easier. Unfortunately, since Excel took over as the dominant spreadsheet, built in LINTERP() type functions have faded in availability.

**tim201110** · 08-25-2018, 12:49 PM

=forecast(e2,forecast(e1,offset($a$3,match(e2,a3:a20)-1,match(e1,a3:u3)-1,1,2),offset($a$3,,match(e1,a3:u3)-1,1,2))*{1;0}+forecast(e1,offset($a$3,match(e2,a3:a20),match(e1,a3:u3)-1,1,2),offset($a$3,,match(e1,a3:u3)-1,1,2))*{0;1},offset($a$3,match(e2,a3:a20),0,-2))

**rabbitfoot345** · 08-27-2018, 08:52 AM

Tried but this is just not working. Start by getting DIV errors (div by 0) and then #N/As - no matter what "score" filling in between. Any suggestions ? Thought it was the A3 cell changing that to A4 did not work either.