+ Reply to Thread
Results 1 to 3 of 3

Linear interpolation between two points found using a lookup funct

  1. #1
    aj4444
    Guest

    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. #2
    Bernard Liengme
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    >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. #3
    Ron Rosenfeld
    Guest

    Re: Linear interpolation between two points found using a lookup funct

    On Tue, 29 Nov 2005 12:31:09 -0800, aj4444 <[email protected]>
    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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1