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 Date | End Date | Value | Month/Year | 12 month Rolling Average | |||
08Mar2007 | 4/4/2007 | 27 | Mar-07 | 27 | |||
02May2007 | 9/6/2007 | 127 | Apr-07 | 27 | |||
28Feb2008 | 3/5/2008 | 6 | May-07 | 27 | |||
29Feb2008 | 8/5/2008 | 158 | Jun-07 | 27 | |||
07Aug2008 | 8/25/2008 | 18 | Jul-07 | 27 | |||
02Mar2009 | 3/4/2009 | 2 | Aug-07 | 27 | |||
12Feb2009 | 4/7/2009 | 54 | Sep-07 | (27+127)/2=77 | |||
30Apr2010 | 5/19/2010 | 19 | Oct-07 | 77 | |||
01Mar2010 | 6/7/2010 | 98 | Nov-07 | 77 | |||
20Aug2010 | 9/9/2010 | 20 | Dec-07 | 77 | |||
20Aug2010 | 10/15/2010 | 56 | Jan-08 | 77 | |||
07Sep2010 | 10/22/2010 | 45 | Feb-08 | 77 | |||
22Jun2010 | 11/29/2010 | 160 | Mar-08 | (27+127+6)/3=53 All three values occurred in 12 month period. | |||
16Nov2010 | 12/2/2010 | 16 | Apr-08 | (127+6)/2=66.5 The 27 value did not occur in 12 month period so it was excluded to derive new average. | |||
18Jan2011 | 1/25/2011 | 7 | May-08 | ||||
21Jan2011 | 2/7/2011 | 17 | Jun-08 | ||||
25Jan2011 | 2/14/2011 | 20 | Jul-08 | ||||
21Jan2011 | 2/18/2011 | 28 | Aug-08 | ||||
16Mar2011 | 3/25/2011 | 9 | Sep-08 | ||||
16Mar2011 | 3/25/2011 | 9 | Oct-08 | ||||
04Dec2009 | 4/14/2011 | 496 | Nov-08 | ||||
01Apr2009 | 5/4/2011 | 763 | Dec-08 | ||||
07Sep2010 | 9/27/2011 | 385 | Jan-09 | ||||
27Jul2011 | 10/3/2011 | 68 | Feb-09 | ||||
29Nov2010 | 12/5/2011 | 371 | Mar-09 |
<tbody>
</tbody>