Average over Last 90 Days - Multiple Sheets

KMason17

New Member
Joined
Feb 25, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
I have created an example version of the spreadsheet I am attempting to create.

Each months sheet looks like what I have posted below. I am looking to use one of these sheets each month. I would then like an analysis tab where I can look at;

-Overall Average of "Test Scores" - (I can and have achieved this)
-Last 90 Day Average of "Test Scores"
-Last 90 Day Average of "Test Scores" for all 4 categories separately
-Last 90 Day Average of Individual People's "Test Scores"
-Last 90 Day Average of Individual People's "Test Scores" for all 4 categories separately

I haven't been able to figure out the 90 Day average, as if I use AVERAGEIFS and 2 "Test Scores" are on the same day then it messes up the average.

Here are the sheets I am using, any help would be appreciated!

(For a bonus I am also considering an additional tab where I can pull these results up using filters and maybe VLOOKUP so that I can pull forward one persons results so that when I do 1 to 1 feedback sessions they aren't looking at other people's scores but I have no idea if I'm even being reasonable. I'm focusing on the 90 Day Average first)

Testing Sheet.xlsx
BCDE
2Under 2 Minute Test
3Test ReferenceDate of TestScore
4Person 155566601/01/20200.0%
5Person 255577701/02/202010.0%
6Person 355588801/03/202020.0%
7Person 455599901/04/202030.0%
8Person 555611001/05/202040.0%
9Person 655622101/06/202050.0%
10Person 755633201/07/202060.0%
11Person 855644301/08/202070.0%
12Person 955655401/09/202080.0%
13Person 1055666501/10/202090.0%
14Person 1155677601/11/2020100.0%
15Person 1255688701/12/202087.5%
16Person 1355699801/01/202187.5%
17Person 1455710901/02/2021100.0%
18Person 1555722001/03/2021100.0%
1961.7%
March '21
Cell Formulas
RangeFormula
E19E19=IFERROR(AVERAGE(E4:E18),"")


Testing Sheet.xlsx
BCDE
225 to 10 Minute Test
23Test ReferenceDate of TestScore
24Person 155566701/01/20200.0%
25Person 255577801/02/202010.0%
26Person 355588901/03/202020.0%
27Person 455600001/04/202030.0%
28Person 555611101/05/202040.0%
29Person 655622201/06/202050.0%
30Person 755633301/07/202060.0%
31Person 855644401/08/202070.0%
32Person 955655501/09/202080.0%
33Person 1055666601/10/202090.0%
34Person 1155677701/11/2020100.0%
35Person 1255688801/12/202087.5%
36Person 1355699901/01/202187.5%
37Person 1455711001/02/2021100.0%
38Person 1555722101/03/2021100.0%
3961.7%
March '21
Cell Formulas
RangeFormula
B24:B38B24=B4
E39E39=IFERROR(AVERAGE(E24:E38),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B24:B38,B44:B58,B64:B78Cell Value=0textNO


Testing Sheet.xlsx
BCDE
42Over 20 Minute Test
43Test ReferenceDate of TestScore
44Person 155566801/01/20200.0%
45Person 255577901/02/202010.0%
46Person 355589001/03/202020.0%
47Person 455600101/04/202030.0%
48Person 555611201/05/202040.0%
49Person 655622301/06/202050.0%
50Person 755633401/07/202060.0%
51Person 855644501/08/202070.0%
52Person 955655601/09/202080.0%
53Person 1055666701/10/202090.0%
54Person 1155677801/11/2020100.0%
55Person 1255688901/12/202087.5%
56Person 1355700001/01/202187.5%
57Person 1455711101/02/2021100.0%
58Person 1555722201/03/2021100.0%
5961.7%
March '21
Cell Formulas
RangeFormula
B44:B58B44=B4
E59E59=IFERROR(AVERAGE(E44:E58),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B24:B38,B44:B58,B64:B78Cell Value=0textNO


Testing Sheet.xlsx
BCDE
62Alternative Test
63Test ReferenceDate of TestScore
64Person 155566901/01/20200.0%
65Person 255578001/02/202010.0%
66Person 355589101/03/202020.0%
67Person 455600201/04/202030.0%
68Person 555611301/05/202040.0%
69Person 655622401/06/202050.0%
70Person 755633501/07/202060.0%
71Person 855644601/08/202070.0%
72Person 955655701/09/202080.0%
73Person 1055666801/10/202090.0%
74Person 1155677901/11/2020100.0%
75Person 1255689001/12/202087.5%
76Person 1355700101/01/202187.5%
77Person 1455711201/02/2021100.0%
78Person 1555722301/03/2021100.0%
7961.7%
March '21
Cell Formulas
RangeFormula
B64:B78B64=B4
E79E79=IFERROR(AVERAGE(E64:E78),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B24:B38,B44:B58,B64:B78Cell Value=0textNO
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Saba Sabaratnam

Board Regular
Joined
May 26, 2018
Messages
196
Hi

I am giving a formula solution to calculate last 90 days average. Hopefully, you can adapt it to solve your problem.

To calculate average of last 90 calendar days, I used the following formula in E2

=AVERAGEIFS(B1:B13,A1:A13,"<="&E1,A1:A13,">="&(E1-90))

1614302030005.png


Kind regards

Saba
 

KMason17

New Member
Joined
Feb 25, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
Thanks for that formula, I had used something similar with "<",TODAY()-90 instead of the E1 scenario you have put in.

I will try yours tomorrow and hopefully I can get it to work for multiple sheets. Does yours solve the problem I mentioned for results on the same day?
 

KMason17

New Member
Joined
Feb 25, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
After some testing I cant get this formula working over multiple sheets. I have received a #VALUE! error.

I believe it would be something to do with pulling multiple dates from multiple areas and therefore not correctly calculating the formula.

So unfortunately I still need help with this solution
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

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