ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   etsimate values of a linear trend for each pair of known values (https://www.excelbanter.com/excel-discussion-misc-queries/65288-etsimate-values-linear-trend-each-pair-known-values.html)

Maarten

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

Bernard Liengme

etsimate values of a linear trend for each pair of known values
 
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




Bernard Liengme

etsimate values of a linear trend for each pair of known values
 
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







All times are GMT +1. The time now is 10:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com