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