+ Reply to Thread
Results 1 to 4 of 4

linear interpolation

  1. #1
    Taha
    Guest

    linear interpolation

    Hi,

    I have a set of data that has monthly (or sometimes biweekly) measurements.
    However, I need to interpolate the daily values using the actual consecutive
    data. However, I do not want to put a best-fit line or curve through the data
    for the whole year. I want the program to pick between two consecutive
    values, and depending on their relationship (linear increase or decrease),
    fill in the gaps between them and spit out the corresponding values. For
    example, I have the following set,

    Date Value
    Jan. 10 0
    Jan. 15 5

    what I need is this:

    Date Value
    Jan. 10 0
    Jan. 11 1
    Jan. 12 2
    Jan. 13 3
    Jan. 14 4
    Jan. 15 5

    However, the next day could be Feb. 2 and the values could be negative and I
    want the program start filling the days between Jan. 15 to Feb. 2 without
    using the Jan. 10 value. I hope I am making myself clear. I dont know if
    there is a program, macro or a function that can do that. I would appreciate
    if you could help me.

    Thanks in advance,

    Taha




  2. #2
    Art
    Guest

    RE: linear interpolation

    You could try this:

    Assume that your dates are in A2:A7 and your values are in B2:B7. B3:B6 are
    blank.

    Put the following formula in B3, and then copy it down for B4,B4,B5 and B6.

    =$B$2+($B$7-$B$2)*(A3-$A$2)/($A$7-$A$2)

    Art
    "Taha" wrote:

    > Hi,
    >
    > I have a set of data that has monthly (or sometimes biweekly) measurements.
    > However, I need to interpolate the daily values using the actual consecutive
    > data. However, I do not want to put a best-fit line or curve through the data
    > for the whole year. I want the program to pick between two consecutive
    > values, and depending on their relationship (linear increase or decrease),
    > fill in the gaps between them and spit out the corresponding values. For
    > example, I have the following set,
    >
    > Date Value
    > Jan. 10 0
    > Jan. 15 5
    >
    > what I need is this:
    >
    > Date Value
    > Jan. 10 0
    > Jan. 11 1
    > Jan. 12 2
    > Jan. 13 3
    > Jan. 14 4
    > Jan. 15 5
    >
    > However, the next day could be Feb. 2 and the values could be negative and I
    > want the program start filling the days between Jan. 15 to Feb. 2 without
    > using the Jan. 10 value. I hope I am making myself clear. I dont know if
    > there is a program, macro or a function that can do that. I would appreciate
    > if you could help me.
    >
    > Thanks in advance,
    >
    > Taha
    >
    >
    >


  3. #3
    Bernd Plumhoff
    Guest

    Re: linear interpolation

    Hi Taha,

    If you insert into cells A1 through D24:

    10-Jan-2005 0.000
    11-Jan-2005 XXX 1 6
    12-Jan-2005 XXX 1 6
    13-Jan-2005 XXX 1 6
    14-Jan-2005 XXX 1 6
    15-Jan-2005 5.000
    16-Jan-2005 XXX 6 24
    ....
    02-Feb-2005 -13.000

    and replace XXX by
    =OFFSET($B$1,C2-1,0)+(OFFSET($B$1,D2-1,0)-OFFSET($B$1,C2-
    1,0))*(A2-OFFSET($A$1,C2-1,0))/(OFFSET($A$1,D2-1,0)-OFFSET
    ($A$1,C2-1,0))

    in cell B2 and copy it onto the other XXX cells, this
    should work fine. Hint: Values in columns C and D tell the
    formula where to look for given values (row number).

    HTH,
    Bernd

  4. #4
    Leo Heuser
    Guest

    Re: linear interpolation

    Hi Taha

    Assuming

    in A1:B100 (headings in A1:B1)

    Date Value
    Jan. 10 0
    Jan. 15 5
    Feb. 2 -12
    etc.


    In D1:E1: headings
    In D2: =A2
    In D3: =D2+1
    Copy D3 down

    In E2 this formula. The formula must be entered/copied as one line.

    =IF(COUNTIF($A$2:$A$100,D2),INDEX($B$2:$B$100,MATCH(D2,$A$2:$A$100,0)),
    E1-((INDEX($B$2:$B$100,MATCH(D2,$A$2:$A$100))-(INDEX($B$2:$B$100,
    MATCH(D2,$A$2:$A$100)+1)))/(INDEX($A$2:$A$100,MATCH(D2,$A$2:$A$100)+1)-
    (INDEX($A$2:$A$100,MATCH(D2,$A$2:$A$100

    Doubleclick the fill handle in E2 (the little square in the lower right
    corner of the cell) to copy the formula down.

    --
    Best Regards
    Leo Heuser

    Followup to newsgroup only please.

    "Taha" <[email protected]> skrev i en meddelelse
    news:[email protected]...
    > Hi,
    >
    > I have a set of data that has monthly (or sometimes biweekly)

    measurements.
    > However, I need to interpolate the daily values using the actual

    consecutive
    > data. However, I do not want to put a best-fit line or curve through the

    data
    > for the whole year. I want the program to pick between two consecutive
    > values, and depending on their relationship (linear increase or decrease),
    > fill in the gaps between them and spit out the corresponding values. For
    > example, I have the following set,
    >
    > Date Value
    > Jan. 10 0
    > Jan. 15 5
    >
    > what I need is this:
    >
    > Date Value
    > Jan. 10 0
    > Jan. 11 1
    > Jan. 12 2
    > Jan. 13 3
    > Jan. 14 4
    > Jan. 15 5
    >
    > However, the next day could be Feb. 2 and the values could be negative and

    I
    > want the program start filling the days between Jan. 15 to Feb. 2 without
    > using the Jan. 10 value. I hope I am making myself clear. I dont know if
    > there is a program, macro or a function that can do that. I would

    appreciate
    > if you could help me.
    >
    > Thanks in advance,
    >
    > Taha
    >
    >
    >






+ 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