+ Reply to Thread
Results 1 to 4 of 4

interpolate

  1. #1
    tom ossieur
    Guest

    interpolate

    Hi,

    column A months
    column B values, but for some months the data are missing

    how to create in column C a table of values assigning values to the missing
    months, based on a linear equation, connecting the last previous value and
    the first next value

    so how to create column C?

    e.g.

    A B C
    Feb-97 27 27
    Mar-97 25
    Apr-97 23 23
    May-97 26 26
    Jun-97 27
    Jul-97 28 28
    Aug-97 27.8
    Sep-97 27.6
    Oct-97 27.4
    Nov-97 27.2
    Dec-97 27 27


    Thanks!

    tom

  2. #2
    Jerry W. Lewis
    Guest

    RE: interpolate

    Assuming that your data are in A1:B11, then C2 can be calculated as

    =FORECAST(ROW(A2),B1:B3,ROW(A1:A3))

    etc.

    If you want a more automatic process, then C2 can contain the array formula
    (entered with Ctrl-Shift-Enter)


    =IF(ISNUMBER(B2),B2,FORECAST(ROW(A2),$B$1:$B$11,IF((ROW($A$1:$A$11)=MAX(IF(ISNUMBER(B$1:B1),ROW(A$1:A1))))+(ROW($A$1:$A$11)=MIN(IF(ISNUMBER(B3:B$11),ROW(A3:A$11)))),ROW($A$1:$A$11))))

    which you can then copy down through C10

    Jerry

    "tom ossieur" wrote:

    > Hi,
    >
    > column A months
    > column B values, but for some months the data are missing
    >
    > how to create in column C a table of values assigning values to the missing
    > months, based on a linear equation, connecting the last previous value and
    > the first next value
    >
    > so how to create column C?
    >
    > e.g.
    >
    > A B C
    > Feb-97 27 27
    > Mar-97 25
    > Apr-97 23 23
    > May-97 26 26
    > Jun-97 27
    > Jul-97 28 28
    > Aug-97 27.8
    > Sep-97 27.6
    > Oct-97 27.4
    > Nov-97 27.2
    > Dec-97 27 27
    >
    >
    > Thanks!
    >
    > tom


  3. #3
    tom ossieur
    Guest

    RE: interpolate

    Hi, thanks!

    option1:
    works only in case at least 2 values are know within a range of 3
    consecutive months
    starting from July 97 I do not get values anymore

    Feb-97 27
    Mar-97 25
    Apr-97 23 23
    May-97 26 26
    Jun-97 27
    Jul-97 28 #DIV/0!
    Aug-97 #DIV/0!
    Sep-97 #DIV/0!
    Oct-97 #DIV/0!
    Nov-97 #DIV/0!
    Dec-97 27 #DIV/0!


    option2:

    Feb-97 27
    Mar-97 #N/A
    Apr-97 23 23
    May-97 26 26
    Jun-97 #VALUE!
    Jul-97 28 28
    Aug-97 #VALUE!
    Sep-97 #VALUE!
    Oct-97 #VALUE!
    Nov-97 #VALUE!
    Dec-97 27 27

    you said, "entered with ctrl-shift-enter? is that the reason? how does it
    work?

    thanks already!

    tom




    "Jerry W. Lewis" wrote:

    > Assuming that your data are in A1:B11, then C2 can be calculated as
    >
    > =FORECAST(ROW(A2),B1:B3,ROW(A1:A3))
    >
    > etc.
    >
    > If you want a more automatic process, then C2 can contain the array formula
    > (entered with Ctrl-Shift-Enter)
    >
    >
    > =IF(ISNUMBER(B2),B2,FORECAST(ROW(A2),$B$1:$B$11,IF((ROW($A$1:$A$11)=MAX(IF(ISNUMBER(B$1:B1),ROW(A$1:A1))))+(ROW($A$1:$A$11)=MIN(IF(ISNUMBER(B3:B$11),ROW(A3:A$11)))),ROW($A$1:$A$11))))
    >
    > which you can then copy down through C10
    >
    > Jerry
    >
    > "tom ossieur" wrote:
    >
    > > Hi,
    > >
    > > column A months
    > > column B values, but for some months the data are missing
    > >
    > > how to create in column C a table of values assigning values to the missing
    > > months, based on a linear equation, connecting the last previous value and
    > > the first next value
    > >
    > > so how to create column C?
    > >
    > > e.g.
    > >
    > > A B C
    > > Feb-97 27 27
    > > Mar-97 25
    > > Apr-97 23 23
    > > May-97 26 26
    > > Jun-97 27
    > > Jul-97 28 28
    > > Aug-97 27.8
    > > Sep-97 27.6
    > > Oct-97 27.4
    > > Nov-97 27.2
    > > Dec-97 27 27
    > >
    > >
    > > Thanks!
    > >
    > > tom


  4. #4
    Jerry W. Lewis
    Guest

    RE: interpolate

    The "etc." under option 1 assumed that you would change the formula in D7 to
    =FORECAST(ROW(A7),B6:B11,ROW(A6:A11))
    so that it would still encompass exactly 2 complete observations.

    As the discussion under option 2 indicated, the price of having a simple
    formula in this case is having to do some hand work to get it to handle all
    cases. Or you could just use option 2 if the complexity of the formula does
    not bother you.

    Jerry

    "tom ossieur" wrote:

    > Hi, thanks!
    >
    > option1:
    > works only in case at least 2 values are know within a range of 3
    > consecutive months
    > starting from July 97 I do not get values anymore
    >
    > Feb-97 27
    > Mar-97 25
    > Apr-97 23 23
    > May-97 26 26
    > Jun-97 27
    > Jul-97 28 #DIV/0!
    > Aug-97 #DIV/0!
    > Sep-97 #DIV/0!
    > Oct-97 #DIV/0!
    > Nov-97 #DIV/0!
    > Dec-97 27 #DIV/0!
    >
    >
    > option2:
    >
    > Feb-97 27
    > Mar-97 #N/A
    > Apr-97 23 23
    > May-97 26 26
    > Jun-97 #VALUE!
    > Jul-97 28 28
    > Aug-97 #VALUE!
    > Sep-97 #VALUE!
    > Oct-97 #VALUE!
    > Nov-97 #VALUE!
    > Dec-97 27 27
    >
    > you said, "entered with ctrl-shift-enter? is that the reason? how does it
    > work?
    >
    > thanks already!
    >
    > tom
    >
    >
    >
    >
    > "Jerry W. Lewis" wrote:
    >
    > > Assuming that your data are in A1:B11, then C2 can be calculated as
    > >
    > > =FORECAST(ROW(A2),B1:B3,ROW(A1:A3))
    > >
    > > etc.
    > >
    > > If you want a more automatic process, then C2 can contain the array formula
    > > (entered with Ctrl-Shift-Enter)
    > >
    > >
    > > =IF(ISNUMBER(B2),B2,FORECAST(ROW(A2),$B$1:$B$11,IF((ROW($A$1:$A$11)=MAX(IF(ISNUMBER(B$1:B1),ROW(A$1:A1))))+(ROW($A$1:$A$11)=MIN(IF(ISNUMBER(B3:B$11),ROW(A3:A$11)))),ROW($A$1:$A$11))))
    > >
    > > which you can then copy down through C10
    > >
    > > Jerry
    > >
    > > "tom ossieur" wrote:
    > >
    > > > Hi,
    > > >
    > > > column A months
    > > > column B values, but for some months the data are missing
    > > >
    > > > how to create in column C a table of values assigning values to the missing
    > > > months, based on a linear equation, connecting the last previous value and
    > > > the first next value
    > > >
    > > > so how to create column C?
    > > >
    > > > e.g.
    > > >
    > > > A B C
    > > > Feb-97 27 27
    > > > Mar-97 25
    > > > Apr-97 23 23
    > > > May-97 26 26
    > > > Jun-97 27
    > > > Jul-97 28 28
    > > > Aug-97 27.8
    > > > Sep-97 27.6
    > > > Oct-97 27.4
    > > > Nov-97 27.2
    > > > Dec-97 27 27
    > > >
    > > >
    > > > Thanks!
    > > >
    > > > tom


+ 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