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

[Geeps]

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

Geeps wrote:

> 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

[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.