Tough One!

[holzie]

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?


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

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel-questions/1099806-calculate-rolling-average.html#post5288262

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).