Re: Linear interpolation between two points found using a lookup funct
- From: Ron Rosenfeld <ronrosenfeld@xxxxxxxxxx>
- Date: Tue, 29 Nov 2005 23:54:16 -0500
On Tue, 29 Nov 2005 12:31:09 -0800, aj4444 <aj4444@xxxxxxxxxxxxxxxxxxxxxxxxx>
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
.
- Prev by Date: Add amount if record contains a value
- Next by Date: Re: Add amount if record contains a value
- Previous by thread: Re: Linear interpolation between two points found using a lookup funct
- Next by thread: Re: Calculation in left corner
- Index(es):
Relevant Pages
|
