Rolling 90 Day Average over Multiple Sheets

KMason17

New Member
Joined
Feb 25, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
I am trying to calculate a rolling 90 day average of results.

Each sheet references a month and are titled as followed January '21, February '21 etc.

Within each of these sheets I collect scores gathered on a certain date. Column C contains date that result was collected and Column D contains result.

I have collected overall average of results using =IFERROR(AVERAGE('March ''21:May "21'!D4:D18),"")

But as I add a sheet for each month I'd like to review a rolling 90 day average so it is relevant for result analysis.

I've used an average of averageifs for each page but that only gives me an average of averages. It doesnt average the scores themselves.

I also tried using =AVERAGEIFS('March ''21:May ''21'!D4:D18,'March ''21:May ''21'!C4:C18,">"&TODAY()-90,'March ''21:May ''21'!C4:C18,"<"&TODAY()) but this was not successful.

Any help would be greatly appreciated.

Each row represents a person that I've collected the result for and my end game would be to be able to calculate rolling averages for each person too
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,813
Office Version
  1. 2010
Platform
  1. Windows
Have you considered using exponential moving averages rather than arithmetic averages. The reason this could help is that to calculate the next value when you add a new value you only need the last times average value and the new value. It makes the calculation very easy.
 

KMason17

New Member
Joined
Feb 25, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
Have you considered using exponential moving averages rather than arithmetic averages. The reason this could help is that to calculate the next value when you add a new value you only need the last times average value and the new value. It makes the calculation very easy.
I will not lie. The information listed above is the very brink of my excel abilities. However I'd love to learn more.

Could you give some information on how I would enact your suggestion?
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,813
Office Version
  1. 2010
Platform
  1. Windows
the equations for an exponential moving average is:
TC = 2 / (Period + 1)
EMA = Lasttime + TC * (Drive - Lasttime
They are used a lot and are very good for averages for a continually moving function they give greater weight to more recent values so are good for detecting changes. They also have the advantage you can change the period ( or the length) of the average just by changing one number
As a start let us just look at one sheet with values in column D from D4 to D18.
In column E put the number 20 in E1
then in E4 put:
Excel Formula:
=D4
this is just to start the moving average
the in E5 put this :
Excel Formula:
=E4+(2/(E$1+1))*(D5-E4)
and copy it down the page.
Then select both columns of data and insert a graph, this will show you what the exponential average is doing.
Now change the number in E1 to 30 and see how it changes the average.
To get this to work over a number of sheet all you need to do is in D3 of each sheet you an equation that points to the value in D18 of the previous sheet i.e.:
for April 21 you would :
Excel Formula:
=March21!D18
in D3
in E3 you put
Excel Formula:
=D3
and finally copy the equation in E5 up to E4.
this will start the moving average at the value from the previous sheet.
you can continue adding sheets for years!!
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,813
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I made a slight error in my last post, instaed of picking D18 from the previous hseet we shoud be picking up E18 which is the last value of the moving average. sorry about that
so the excel formula in D3 should be:
Excel Formula:
=March21!E18
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,813
Office Version
  1. 2010
Platform
  1. Windows
The 20 in E1 is used as the value for the period in the exponential moving average. It is a measure of how many values are included in the average. since a exponential moving average gives more weight to more recent values, a 20 period EMa ( exponential moving average) changes more rapidly than a 20 period arithmetic average.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,921
Messages
5,639,016
Members
417,062
Latest member
CM214

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
Top