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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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

O
  • Question Question
Calculating Average # of Procedures per Week
Replies
2
Views
263
RobP
R
M
  • Question Question
Pivot Table Calculated Field Average and Std Deviation (control chart)
Replies
4
Views
225
Sergius
S
D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
159
Domenic
D
S
  • Solved
How to calculate the Average with Condition of Networking days from the reference dates
Replies
2
Views
364
shoun2502
S
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
130
Denny57
D

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

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