# Automating Linear and Growth Trend Auto Fill

#### ronnie76

##### Board Regular
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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Months00.030.10.250.51234567891011121518212427303336
2Score1010109887
3LinearPredicted1010109887
4GrowthPredicted1010109887
5AutofilledLinearinRed10101098877.066.666.275.885.485.094.74.33.913.513.122.732.331.941.551.15
6AutofilledGrowthinblue10101098877.146.826.526.235.955.695.435.194.964.744.534.334.143.953.783.61
7Actualscores10101098877554456
Sheet1

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### whiskycan

##### New Member
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

##### Board Regular
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
ABCDEFGHIJKLMNOPQRSTUV
1Months000.10.30.512345678910111215182124
2Score109.59.3998.88.587.5
3Using=TREND(\$B\$2:\$J\$2,\$B\$1:\$J\$1,K1,TRUE)109.59.3998.88.587.576.565.554.543.520.6-0.9-2.4
4Using=GROWTH(\$B\$2:\$J\$2,\$B\$1:\$J\$1,K1,TRUE)109.59.3998.88.587.57.16.76.365.65.354.743.42.82.4
5AutofilledLinearinRed109.59.3998.88.587.57.57.26.96.76.46.15.95.65.354.84.5
6AutofilledGrowthinblue109.59.3998.88.587.57.57.37.16.96.76.56.36.15.95.75.55.4
7Using=TREND(\$G\$2:J2,\$G\$1:J1,K1)7.1#################################
8Using=GROWTH(\$G\$2:J2,\$G\$1:J1,K1)7.2#################################
Sheet1

#### whiskycan

##### New Member
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

##### Board Regular
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.

Replies
7
Views
435
Replies
8
Views
616
Replies
5
Views
968
Replies
0
Views
693
Replies
3
Views
768

1,190,633
Messages
5,982,040
Members
439,751
Latest member
sohamkhatri

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back