Linear interpolation between two points found using a lookup funct

[aj4444]

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?

 

[Bernard Liengme]

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

 

[Ron Rosenfeld]

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