Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linear interpolation function in excel | Excel Worksheet Functions | |||
I am looking for a function for linear interpolation | Excel Discussion (Misc queries) | |||
Lookup returns message box when an exact match is not found | Excel Worksheet Functions | |||
linear interpolation | Excel Discussion (Misc queries) | |||
double lookup, nest, or macro? | Excel Worksheet Functions |