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(B20,B2,INDEX(myvalues,MATCH(A2,mydates))+(IND EX(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" wrote in message
...
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" wrote in message
...
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(B20,B2,INDEX(myvalues,MATCH(A2,mydates))+(IND EX(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" wrote in message
...
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