Calculate Rolling Average

holzie

New Member
Joined
Apr 23, 2015
Messages
19
I have a data set that I am trying to calculate a moving average. In the attached picture the Data set is the date started, date ended, and column C is the simply the number of days between those dates. The moving average I am trying get is a rolling 12 month average of Column C shown on a monthly basis. See highlighted yellow area of what I would like to see it outputted. Any ideas on how to do this?
view
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
I have went through several videos and in most of the videos there is a monthly average determined already. I think the first problem is I need help calculating the monthly averages. Can you see the pic I posted and what I am trying to get to?
 
Upvote 0
Ok, This might be the best I can do for displaying what I have.


Start DateEnd DateValue Month/Year12 month Rolling Average
08Mar20074/4/200727 Mar-07
02May20079/6/2007127 Apr-07
28Feb20083/5/20086 May-07
29Feb20088/5/2008158 Jun-07
07Aug20088/25/200818 Jul-07
02Mar20093/4/20092 Aug-07
12Feb20094/7/200954 Sep-07
30Apr20105/19/201019 Oct-07
01Mar20106/7/201098 Nov-07
20Aug20109/9/201020 Dec-07
20Aug201010/15/201056 Jan-08
07Sep201010/22/201045 Feb-08
22Jun201011/29/2010160 Mar-08
16Nov201012/2/201016 Apr-08
18Jan20111/25/20117 May-08
21Jan20112/7/201117 Jun-08
25Jan20112/14/201120 Jul-08
21Jan20112/18/201128 Aug-08
16Mar20113/25/20119 Sep-08
16Mar20113/25/20119 Oct-08
04Dec20094/14/2011496 Nov-08
01Apr20095/4/2011763 Dec-08
07Sep20109/27/2011385 Jan-09
27Jul201110/3/201168 Feb-09
29Nov201012/5/2011371 Mar-09

<colgroup><col><col span="2"><col span="3"><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
I have a data set that I am trying to calculate a moving average. In the below Data set is the date started, date ended, and value is the simply the number of days between those dates. The moving average I am trying get is a rolling 12 month average shown on a monthly basis. See column of 12 month rolling average that I have started to calculate manually. Any thoughts on how to do this more automatically?



<tbody></tbody>
Start DateEnd DateValueMonth/Year12 month Rolling Average
08Mar20074/4/200727Mar-0727
02May20079/6/2007127Apr-0727
28Feb20083/5/20086May-0727
29Feb20088/5/2008158Jun-0727
07Aug20088/25/200818Jul-0727
02Mar20093/4/20092Aug-0727
12Feb20094/7/200954Sep-07(27+127)/2=77
30Apr20105/19/201019Oct-0777
01Mar20106/7/201098Nov-0777
20Aug20109/9/201020Dec-0777
20Aug201010/15/201056Jan-0877
07Sep201010/22/201045Feb-0877
22Jun201011/29/2010160Mar-08(27+127+6)/3=53 All three values occurred in 12 month period.
16Nov201012/2/201016Apr-08(127+6)/2=66.5 The 27 value did not occur in 12 month period so it was excluded to derive new average.
18Jan20111/25/20117May-08
21Jan20112/7/201117Jun-08
25Jan20112/14/201120Jul-08
21Jan20112/18/201128Aug-08
16Mar20113/25/20119Sep-08
16Mar20113/25/20119Oct-08
04Dec20094/14/2011496Nov-08
01Apr20095/4/2011763Dec-08
07Sep20109/27/2011385Jan-09
27Jul201110/3/201168Feb-09
29Nov201012/5/2011371Mar-09


<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top