Calculate Rolling Average

H

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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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.


[TABLE="width: 798"]
<colgroup><col><col span="2"><col span="3"><col><col></colgroup><tbody>[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] [/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] [/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] [/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] [/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] [/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] [/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] [/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] [/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] [/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] [/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] [/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] [/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] [/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] [/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]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
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?


[TABLE="class: cms_table, width: 798"]
<tbody></tbody>[/TABLE]
[TABLE="class: 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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Calculating Employee Turnover to Date from 2 Table
Replies
1
Views
37
Cubist
Cubist
O
  • Question Question
Calculating Average # of Procedures per Week
Replies
2
Views
306
RobP
R
M
  • Question Question
Pivot Table Calculated Field Average and Std Deviation (control chart)
Replies
4
Views
285
Sergius
S
S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
282
Tetra201
Tetra201
R
  • Question Question
Auto deleting a date column once the date has passed via VBA
Replies
4
Views
96
RMH2024
R

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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