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

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),R OW(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

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),R OW(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

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),R OW(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