Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
aj4444
 
Posts: n/a
Default Linear interpolation between two points found using a lookup funct

I need to interpolate two points (x0,y0) & (x1,y1) to find the y value for a
given x. First, I need to find x0 and x1 from a column using a lookup
function. The x's column increases as you go down.

For example given a data set:

x's y's
20.33 5
22.69 7
25.06 8
27.42 18
29.79 37

Say I want to find y for x=23. I want to lookup the two closest values of
x's that x=23 is between(22.69 and 25.06) and then use these two points to
linearly interpolate the y for x=23. Any suggestions?


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
 
Posts: n/a
Default Linear interpolation between two points found using a lookup funct

I put you 'table' in A1:B5
In E1, I enter the x value (23)
In E2, I found x0 using =VLOOKUP(E1,$A$1:$B$5,1)
In F2, I found y0 with =VLOOKUP(E1,$A$1:$B$5,2)
In E3, I found x1 with =INDEX(A1:A5,MATCH(E2,A1:A5)+1)
In F3, I found y1 with =INDEX(B1:B5,MATCH(F2,B1:B5)+1)
These can be used to do the interpolation.
With care one could put everything in one formula but debugging with be
bu... (sorry, problem)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"aj4444" wrote in message
...
I need to interpolate two points (x0,y0) & (x1,y1) to find the y value for
a
given x. First, I need to find x0 and x1 from a column using a lookup
function. The x's column increases as you go down.

For example given a data set:

x's y's
20.33 5
22.69 7
25.06 8
27.42 18
29.79 37

Say I want to find y for x=23. I want to lookup the two closest values of
x's that x=23 is between(22.69 and 25.06) and then use these two points to
linearly interpolate the y for x=23. Any suggestions?




  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Linear interpolation between two points found using a lookup funct

On Tue, 29 Nov 2005 12:31:09 -0800, aj4444
wrote:

I need to interpolate two points (x0,y0) & (x1,y1) to find the y value for a
given x. First, I need to find x0 and x1 from a column using a lookup
function. The x's column increases as you go down.

For example given a data set:

x's y's
20.33 5
22.69 7
25.06 8
27.42 18
29.79 37

Say I want to find y for x=23. I want to lookup the two closest values of
x's that x=23 is between(22.69 and 25.06) and then use these two points to
linearly interpolate the y for x=23. Any suggestions?


=IF(NewX=MAX(x_s),MAX(y_s),VLOOKUP(NewX,tbl,2)+
(INDEX(tbl,MATCH(VLOOKUP(NewX,tbl,1),x_s)+2,2)-
VLOOKUP(NewX,tbl,2))*(NewX-VLOOKUP(NewX,tbl,1))
/(INDEX(tbl,MATCH(VLOOKUP(NewX,tbl,1),x_s)+2,1)-
VLOOKUP(NewX,tbl,1)))

If your data is in A1:B6, then:

tbl =Sheet1!$A$1:$B$6
x_s =Sheet1!$A$2:$A$6
y_s =Sheet1!$B$2:$B$6


NewX can be any cell.

Errors will be output if NewX is outside of the range of x_s.


--ron
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


Similar Threads
Thread Thread Starter Forum Replies Last Post
linear interpolation function in excel tskoglund Excel Worksheet Functions 4 September 10th 05 03:31 AM
I am looking for a function for linear interpolation azad Excel Discussion (Misc queries) 1 July 17th 05 09:18 PM
Lookup returns message box when an exact match is not found JFeeman Excel Worksheet Functions 3 February 11th 05 08:19 PM
linear interpolation Taha Excel Discussion (Misc queries) 3 January 31st 05 03:12 PM
double lookup, nest, or macro? Josef.angel Excel Worksheet Functions 1 October 29th 04 09:50 AM


All times are GMT +1. The time now is 11:45 AM.

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"