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