software

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 quot;b1 (b$5-b$1)/(ROW(b$5)-ROW(b$1))quot;, 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(B2gt;0,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

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

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