Rolling 90 Day Average over Multiple Sheets

[KMason17]

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

 

[offthelip]

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]

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]

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:

=D4

this is just to start the moving average
the in E5 put this :

=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 :

=March21!D18

in D3
in E3 you put

=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]

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:

=March21!E18

 

[KMason17]

Why do you use the 20 in E1?

 

[offthelip]

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.