How to interpolate in an array forX and Y values to get Z?

[Guest]

I am interpolating in an array
for ex,
5 6 8 9
2 22.5 23.4 25.6 28
3 20.2 21.5 22.6 23
4 19 20 21.2 22
5 18 19.2 20 20.5

The First Row is (Xvalues) and the first coumn (Yvalues)
For ex, For X=5.5 and Y=2.5, the interpolated value will be 21.90. How can i
write a formula for this? i would appreciate your help.
Thanks so much in advance

 

[Jerry W. Lewis]

The data layout does not lend itself to a simple formula. Assuming that
the table is in A1:E5, you could interpolate with either

=FORECAST(5.5,B2:C2,B$1:C$1)+(2.5-A2)/(A3-A2)*(FORECAST(5.5,B3:C3,B$1:C$1)-FORECAST(5.5,B2:C2,B$1:C$1))
or
=(A3-2.5)/(A3-A2)*FORECAST(5.5,B2:C2,B$1:C$1)+(2.5-A2)/(A3-A2)*FORECAST(5.5,B3:C3,B$1:C$1)

In general you can use FORECAST for 1-D linear interpolation and TREND
for multi-D linear interpolation if the data are layed out appropriately.

Jerry

 

[MrShorty]

The hardest part of writing an interpolation function in Excel is the
lookup part to locate the interval that contains X and Y. Once you've
decided which interval to look in (in this case 5<X<6, 2<Y<3), then
Jerry's formula works just fine. If you decide to look in a different
interval (X=8.5, Y=3.5), then you would need to modify the cell
references to interpolate over the correct interval. If editing the
formula each time you change intervals doesn't bother you, then this
may be the easiest approach. (IE you're using the computer between
your ears to do the lookup part).
If you want to automate it further, the easiest way is to build a new
table which uses MATCH/INDEX combinations to return the boundary values
of the interval of interest, then use JErry's formula (referencing the
new subtable) to perform the interpolation.