Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Geeps
 
Posts: n/a
Default How to interpolate in an array forX and Y values to get Z?

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jerry W. Lewis
 
Posts: n/a
Default How to interpolate in an array forX and Y values to get Z?

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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MrShorty
 
Posts: n/a
Default How to interpolate in an array forX and Y values to get Z?


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.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=492672

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 06:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"