Automating Linear and Growth Trend Auto Fill

[ronnie76]

Hello everyone. New to VBA so pardon if there is a similar post with the answer that I was unable to understand. I searched and did not find anything to help me or that I understood would help anyway. I used HTML Maker to show my sheet below. I want to have cells K3:Z3 automatically populated using the same calculations as "Linear Trend" Auto Fill and K4:Z4 using "Growth Trend" Auto Fill without having to do it manually. The data in Row 2 will of course be continually updated with actual scores so the remaining empty month scores would need to be updated again. I am using this to come up with a more accurate prediction method than a simple trend line in Excel graphs. Thanks for any and all help.

Auto Fill setup for questions.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Months	0	0.03	0.1	0.25	0.5	1	2	3	4	5	6	7	8	9	10	11	12	15	18	21	24	27	30	33	36
2	Score	10		10	10		9	8	8	7
3	LinearPredicted	10		10	10		9	8	8	7
4	GrowthPredicted	10		10	10		9	8	8	7
5	AutofilledLinearinRed	10		10	10		9	8	8	7	7.06	6.66	6.27	5.88	5.48	5.09	4.7	4.3	3.91	3.51	3.12	2.73	2.33	1.94	1.55	1.15
6	AutofilledGrowthinblue	10		10	10		9	8	8	7	7.14	6.82	6.52	6.23	5.95	5.69	5.43	5.19	4.96	4.74	4.53	4.33	4.14	3.95	3.78	3.61
7	Actualscores	10		10	10		9	8	8	7	7	5	5	4	4	5	6
Sheet1

 

[whiskycan]

Not sure I understand exactly what you mean, however I think you are looking to use the GROWTH and TREND functions.

Something like:
=TREND($G$2:J2,$G$1:J1,K1)
and
=GROWTH($G$2:J2,$G$1:J1,K1)

in cell K3 might do the trick?

 

[ronnie76]

Thank you for your help whiskycan. I have tried using the GROWTH, TREND, and FORECAST funtions in Excel, but they do not give the same results as Autofill Linear and Growth if you drag and drop manually. Below in the grey shaded areas are where I used Autofill by dragging and dropping. These are the results I need because as you can see using the functions the numbers are much lower that the autofill.

Thanks again for the help.

Auto Fill setup for questions.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Months	0	0	0.1	0.3	0.5	1	2	3	4	5	6	7	8	9	10	11	12	15	18	21	24
2	Score	10	9.5	9.3	9	9	8.8	8.5	8	7.5
3	Using=TREND($B$2:$J$2,$B$1:$J$1,K1,TRUE)	10	9.5	9.3	9	9	8.8	8.5	8	7.5	7	6.5	6	5.5	5	4.5	4	3.5	2	0.6	-0.9	-2.4
4	Using=GROWTH($B$2:$J$2,$B$1:$J$1,K1,TRUE)	10	9.5	9.3	9	9	8.8	8.5	8	7.5	7.1	6.7	6.3	6	5.6	5.3	5	4.7	4	3.4	2.8	2.4
5	AutofilledLinearinRed	10	9.5	9.3	9	9	8.8	8.5	8	7.5	7.5	7.2	6.9	6.7	6.4	6.1	5.9	5.6	5.3	5	4.8	4.5
6	AutofilledGrowthinblue	10	9.5	9.3	9	9	8.8	8.5	8	7.5	7.5	7.3	7.1	6.9	6.7	6.5	6.3	6.1	5.9	5.7	5.5	5.4
7	Using=TREND($G$2:J2,$G$1:J1,K1)										7.1	###	###	###	###	###	###	###	###	###	###	###
8	Using=GROWTH($G$2:J2,$G$1:J1,K1)										7.2	###	###	###	###	###	###	###	###	###	###	###
Sheet1

 

[whiskycan]

Hi Ronnie,

I think the reason you are getting different results is because your entries in the month row is non-linear, i.e it goes:

0,0,0.1,0.3,0.5,1,2,3,4,5,6,7,8,9,10,11,12,15,18,21...

When you use the autofill, it assumes your data will be in the format 1,2,3,4,5,...

The formulas are smarter and take the values in the months row into account

So technically the autofill is incorrect rather than the TREND function.

Hope this helps.

W

 

[ronnie76]

Hi,

Yes I noticed that yesterday afternoon. I did some tests and you are correct. My problem now however is that the not so smart AUTOFILL is actually much more accurate at predicting future scores. I have tested and I am just going to fool the formula by leaving the fraction of a month calculations out.

Thanks again for all of your help.