How to automatically create a 12 months continuing rolling average

[Corried]

As date comes in. How can I have a continuous rolling moving average from the last cell, going forward?

See my error below on cell "D"
How can fix this?

This in advance.

 

[Corried]

12/1/2018​	51.50​	-0.10​	50.51​
1/1/2019​	48.50​	-3.00​	50.42​
2/1/2019​	49.50​	1.00​	50.61​
3/1/2019​	48.90​	-0.60​	50.73​
4/1/2019​	51.00​	2.10​	50.96​
5/1/2019​	50.80​	-0.20​	50.96​
6/1/2019​	49.20​	-1.60​	50.98​
7/1/2019​	51.50​	2.30​	51.34​
8/1/2019​	50.30​	-1.20​	51.30​
9/1/2019​	50.90​	0.60​	51.63​
10/1/2019​	51.10​	0.20​	52.00​
11/1/2019​	52.90​	1.80​	52.90​

 

[MARK858]

The formula posted does a rolling average as I stated based on column C. If it needs to be B then change all the C's to B's.
The formula will drag down if you increase the 64 to a higher number than you ever need as long as you don't put the date in column A in advance (as I have already stated).

What are the column letters for each column in the table that you have posted in post number 11 and what are the row numbers that you are showing (it really would be better if you used XL2BB)?

Please note that the numbers in your sample in column E (E at a guess) do not match a rolling year based on the data shown (the first line would be 51.50) so I am guessing that there were more dates within a year above the data shown.

Table below based on my guesses below and with the last date changed so it is more than 1 year from the first date (please note that my dates are in dd/mm/yyyy format). Manually done Average formula in column F to test results.

Dates in Column B, Column being averaged Column C and result in Column E. Upper range increased to row 154.

Book1
	B	C	D	E	F
43	01/12/2018	51.5	-0.1	51.5	51.5
44	01/01/2019	48.5	-3	50	50
45	01/02/2019	49.5	1	49.83333333	49.83333333
46	01/03/2019	48.9	-0.6	49.6	49.6
47	01/04/2019	51	2.1	49.88	49.88
48	01/05/2019	50.8	-0.2	50.03333333	50.03333333
49	01/06/2019	49.2	-1.6	49.91428571	49.91428571
50	01/07/2019	51.5	2.3	50.1125	50.1125
51	01/08/2019	50.3	-1.2	50.13333333	50.13333333
52	01/09/2019	50.9	0.6	50.21	50.21
53	01/10/2019	51.1	0.2	50.29090909	50.29090909
54	01/01/2020	52.9	1.8	50.41818182	50.41818182
Sheet1

Cell Formulas
Range	Formula
E43:E54	E43	=AVERAGEIFS($C$43:$C$154,$B$43:$B$154,"<="&$B43,$B$43:$B$154,">="&DATE(YEAR($B43)-1,MONTH($B43),DAY($B43)))
F43:F53	F43	=AVERAGE($C$43:$C43)
F54	F54	=AVERAGE($C$44:$C54)

 

[Corried]

Book1
	A	B	C	D	E
1	Country	Column1	Last	Change	12 Mth Rolling Avg.
2		8/1/2015	50.10
3	Colombia	9/1/2015	50.00	-0.10
4	Colombia	10/1/2015	51.60	1.60	51.86
5	Colombia	11/1/2015	52.20	0.60	51.75
6	Colombia	12/1/2015	53.50	1.30	51.61
7	Colombia	1/1/2016	54.30	0.80	51.37
8	Colombia	11/19/2020	52.90	-1.40	51.05
9		3/1/2016	52.10	-0.80	50.75
10		4/1/2016	51.20	-0.90	50.51
11		5/1/2016	50.90	-0.30	50.19
12		6/1/2016	50.40	-0.50	49.73
13		7/1/2016	50.70	0.30	49.38
14		8/1/2016	50.40	-0.30	49.00
15		9/1/2016	52.10	1.70	48.63
16		10/1/2016	50.30	-1.80	48.32
17		11/1/2016	50.50	0.20	48.23
18		12/1/2016	50.60	0.10	48.03
19		1/1/2017	50.50	-0.10	47.88
20		2/1/2017	49.30	-1.20	47.59
21		3/1/2017	49.20	-0.10	47.63
22		4/1/2017	47.40	-1.80	47.75
23		5/1/2017	45.40	-2.00	48.14
24		6/1/2017	46.10	0.70	48.62
25		7/1/2017	46.20	0.10	49.19
26		8/1/2017	46.00	-0.20	49.80
27		9/1/2017	48.30	2.30	50.41
28		10/1/2017	49.30	1.00	50.78
29		11/1/2017	48.10	-1.20	51.01
30		12/1/2017	48.80	0.70	51.30
31		1/1/2018	47.00	-1.80	51.53
32		2/1/2018	49.70	2.70	51.65
33		3/1/2018	50.70	1.00	51.63
34		4/1/2018	52.10	1.40	51.48
35		5/1/2018	51.10	-1.00	51.39
36		6/1/2018	53.00	1.90	51.37
37		7/1/2018	53.50	0.50	51.05
38		8/1/2018	53.30	-0.20	50.88
39		9/1/2018	52.80	-0.50	50.63
40		10/1/2018	52.00	-0.80	50.48
41		11/1/2018	51.60	-0.40	50.40
42		12/1/2018	51.50	-0.10	50.51
43		1/1/2019	48.50	-3.00	50.42
44		2/1/2019	49.50	1.00	50.61
45		3/1/2019	48.90	-0.60	50.73
46		4/1/2019	51.00	2.10	50.96
47		5/1/2019	50.80	-0.20	50.96
48		6/1/2019	49.20	-1.60	50.98
49		7/1/2019	51.50	2.30	51.34
50		8/1/2019	50.30	-1.20	51.30
51		9/1/2019	50.90	0.60	51.63
52		10/1/2019	51.10	0.20	52.00
53		11/1/2019	52.90	1.80	52.90
Colombia

Cell Formulas
Range	Formula
D3, D4:D53	D3	=C3-C2
E4:E53	E4	=AVERAGE(C4:C15)

 

[Corried]

.Not sure if you are reject my plead. Please view the true excel worksheet below. I am having some challenges on getting around this.

 

[MARK858]

So judging by cell E4's you are looking for 1 year (actually 11 months not 1 year) after the date in C4 not the previous year... correct?
and why are you counting 11/19/2020 in your Average formula (cell B8)?
Your results in your table with the exception of where you have the 11/19/2020 affecting the result are correct. If you correct the offending cell to 2/1/2016 then it is all correct (obviously my dates below are in dd/mm/yyyy format.

Book1
	A	B	C	D	E	F
1	Country	Column1	Last	Change	12 Mth Rolling Avg.
2		01/08/2015	50.10			51.65833333
3	Colombia	01/09/2015	50.00	-0.1		51.68333333
4	Colombia	01/10/2015	51.60	1.6	51.85833333	51.85833333
5	Colombia	01/11/2015	52.20	0.6	51.75	51.75
6	Colombia	01/12/2015	53.50	1.3	51.60833333	51.60833333
7	Colombia	01/01/2016	54.30	0.8	51.36666667	51.36666667
8	Colombia	01/02/2016	52.90	-1.4	51.05	51.05
9		01/03/2016	52.10	-0.8	50.75	50.75
10		01/04/2016	51.20	-0.9	50.50833333	50.50833333
11		01/05/2016	50.90	-0.3	50.19166667	50.19166667
12		01/06/2016	50.40	-0.5	49.73333333	49.73333333
13		01/07/2016	50.70	0.3	49.375	49.375
14		01/08/2016	50.40	-0.3	49	49
15		01/09/2016	52.10	1.7	48.63333333	48.63333333
16		01/10/2016	50.30	-1.8	48.31666667	48.31666667
17		01/11/2016	50.50	0.2	48.23333333	48.23333333
18		01/12/2016	50.60	0.1	48.03333333	48.03333333
19		01/01/2017	50.50	-0.1	47.88333333	47.88333333
20		01/02/2017	49.30	-1.2	47.59166667	47.59166667
21		01/03/2017	49.20	-0.1	47.625	47.625
22		01/04/2017	47.40	-1.8	47.75	47.75
23		01/05/2017	45.40	-2	48.14166667	48.14166667
24		01/06/2017	46.10	0.7	48.61666667	48.61666667
25		01/07/2017	46.20	0.1	49.19166667	49.19166667
26		01/08/2017	46.00	-0.2	49.8	49.8
27		01/09/2017	48.30	2.3	50.40833333	50.40833333
28		01/10/2017	49.30	1	50.78333333	50.78333333
29		01/11/2017	48.10	-1.2	51.00833333	51.00833333
30		01/12/2017	48.80	0.7	51.3	51.3
31		01/01/2018	47.00	-1.8	51.525	51.525
32		01/02/2018	49.70	2.7	51.65	51.65
33		01/03/2018	50.70	1	51.63333333	51.63333333
34		01/04/2018	52.10	1.4	51.48333333	51.48333333
35		01/05/2018	51.10	-1	51.39166667	51.39166667
36		01/06/2018	53.00	1.9	51.36666667	51.36666667
37		01/07/2018	53.50	0.5	51.05	51.05
38		01/08/2018	53.30	-0.2	50.88333333	50.88333333
39		01/09/2018	52.80	-0.5	50.63333333	50.63333333
40		01/10/2018	52.00	-0.8	50.475	50.475
41		01/11/2018	51.60	-0.4	50.4	50.4
42		01/12/2018	51.50	-0.1	50.50833333	50.50833333
43		01/01/2019	48.50	-3	50.41818182	50.41818182
44		01/02/2019	49.50	1	50.61	50.61
45		01/03/2019	48.90	-0.6	50.73333333	50.73333333
46		01/04/2019	51.00	2.1	50.9625	50.9625
47		01/05/2019	50.80	-0.2	50.95714286	50.95714286
48		01/06/2019	49.20	-1.6	50.98333333	50.98333333
49		01/07/2019	51.50	2.3	51.34	51.34
50		01/08/2019	50.30	-1.2	51.3	51.3
51		01/09/2019	50.90	0.6	51.63333333	51.63333333
52		01/10/2019	51.10	0.2	52	52
53		01/11/2019	52.90	1.8	52.9	52.9
Sheet1

Cell Formulas
Range	Formula
D3, D4:D53	D3	=C3-C2
F2:F3, F4:F53	F2	=AVERAGEIFS($C$2:$C$154,$B$2:$B$154,">="&B2,$B$2:$B$154,"<"&DATE(YEAR(B2),MONTH(B2)+11,DAY(B2))+1)
E4:E53	E4	=AVERAGE(C4:C15)

 

[Corried]

MARK858
Thanks for your reply.

The code works wonderful. I appreciate your time and effort in getting my problem solved.

Thanks again

 

[mikerickson]

That sounds like you should use the formula =AVERAGE(C43:D53) in D53
Then when you add 1/1/2020 in B54 and drag the formula down, it will become =AVERAGE(C44:D54).

 

[MARK858]

I agree totally with Mike. If your entries are consistently always 1 per month as per your posted data you should be using the simpler and more efficient Average formula.