# Rolling 90 Day Average over Multiple Sheets

#### KMason17

##### New Member
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### offthelip

##### Well-known Member
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
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
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

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``

#### KMason17

##### New Member
Why do you use the 20 in E1?

#### offthelip

##### Well-known Member
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.

Replies
3
Views
46
Replies
10
Views
462
Replies
3
Views
418
Replies
0
Views
23
Replies
5
Views
98

### Forum statistics

1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

### 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.

### Which adblocker are you using?

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

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