etsimate values of a linear trend for each pair of known values

Hi,
is there a way to automatically fill in the blank cells between two numbers
in a column B using a linear regression between those two points based on
culumn A?
Column A, B1, B5 and B7 are given. Column C (or B) should contain the results.
I could use "b1+(b$5-b$1)/(ROW(b$5)-ROW(b$1))", but then I have to change
the formula after each pair of values B and my data go for 3 years...
A B C
1-jan 10 10
2-jan 12,5
3-jan 15
4-jan 17,5
5-jan 20 20
6-jan 14
7-jan 8 8
8-jan
9-jan

Thanks
Maarten

1) copy the data to another sheet (say A1:B5000)
2) use sort or filter to remove rows with blank values (leaving A1:B1000)
3) name the range with the dates as mydates (say A1:A1000), the range with
number (say B1:B1000) myvalues, the entire range (say A1:B1000) mytable
4) On the main sheet, replace C2 with
=IF(B2>0,B2,INDEX(myvalues,MATCH(A2,mydates))+(INDEX(myvalues,MATCH(A2,mydates)+1)-INDEX(myvalues,MATCH(A2,mydates)))/(INDEX(mydates,MATCH(A2,mydates)+1)-INDEX(mydates,MATCH(A2,mydates)))*(A2-INDEX(mydates,MATCH(A2,mydates))))

Actally this works except for the last few rows of your data (when there is
no second date to follow)
=INDEX(myvalues,MATCH(A2,mydates))+(INDEX(myvalues,MATCH(A2,mydates)+1)-INDEX(myvalues,MATCH(A2,mydates)))/(INDEX(mydates,MATCH(A2,mydates)+1)-INDEX(mydates,MATCH(A2,mydates)))*(A2-INDEX(mydates,MATCH(A2,mydates)))
It worked well for me
best wsihes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Maarten" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> is there a way to automatically fill in the blank cells between two
> numbers
> in a column B using a linear regression between those two points based on
> culumn A?
> Column A, B1, B5 and B7 are given. Column C (or B) should contain the
> results.
> I could use "b1+(b$5-b$1)/(ROW(b$5)-ROW(b$1))", but then I have to change
> the formula after each pair of values B and my data go for 3 years...
> A B C
> 1-jan 10 10
> 2-jan 12,5
> 3-jan 15
> 4-jan 17,5
> 5-jan 20 20
> 6-jan 14
> 7-jan 8 8
> 8-jan
> 9-jan
>
> Thanks
> Maarten

forgot: you need to format C2 as General (or number as needed)
copy down the column

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" <[email protected]> wrote in message
news:[email protected]...
> 1) copy the data to another sheet (say A1:B5000)
> 2) use sort or filter to remove rows with blank values (leaving A1:B1000)
> 3) name the range with the dates as mydates (say A1:A1000), the range with
> number (say B1:B1000) myvalues, the entire range (say A1:B1000) mytable
> 4) On the main sheet, replace C2 with
> =IF(B2>0,B2,INDEX(myvalues,MATCH(A2,mydates))+(INDEX(myvalues,MATCH(A2,mydates)+1)-INDEX(myvalues,MATCH(A2,mydates)))/(INDEX(mydates,MATCH(A2,mydates)+1)-INDEX(mydates,MATCH(A2,mydates)))*(A2-INDEX(mydates,MATCH(A2,mydates))))
>
> Actally this works except for the last few rows of your data (when there
> is no second date to follow)
> =INDEX(myvalues,MATCH(A2,mydates))+(INDEX(myvalues,MATCH(A2,mydates)+1)-INDEX(myvalues,MATCH(A2,mydates)))/(INDEX(mydates,MATCH(A2,mydates)+1)-INDEX(mydates,MATCH(A2,mydates)))*(A2-INDEX(mydates,MATCH(A2,mydates)))
> It worked well for me
> best wsihes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Maarten" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>> is there a way to automatically fill in the blank cells between two
>> numbers
>> in a column B using a linear regression between those two points based on
>> culumn A?
>> Column A, B1, B5 and B7 are given. Column C (or B) should contain the
>> results.
>> I could use "b1+(b$5-b$1)/(ROW(b$5)-ROW(b$1))", but then I have to change
>> the formula after each pair of values B and my data go for 3 years...
>> A B C
>> 1-jan 10 10
>> 2-jan 12,5
>> 3-jan 15
>> 4-jan 17,5
>> 5-jan 20 20
>> 6-jan 14
>> 7-jan 8 8
>> 8-jan
>> 9-jan
>>
>> Thanks
>> Maarten
>
>