Average over Last 90 Days - Multiple Sheets

[KMason17]

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
	B	C	D	E
2		Under 2 Minute Test
3		Test Reference	Date of Test	Score
4	Person 1	555666	01/01/2020	0.0%
5	Person 2	555777	01/02/2020	10.0%
6	Person 3	555888	01/03/2020	20.0%
7	Person 4	555999	01/04/2020	30.0%
8	Person 5	556110	01/05/2020	40.0%
9	Person 6	556221	01/06/2020	50.0%
10	Person 7	556332	01/07/2020	60.0%
11	Person 8	556443	01/08/2020	70.0%
12	Person 9	556554	01/09/2020	80.0%
13	Person 10	556665	01/10/2020	90.0%
14	Person 11	556776	01/11/2020	100.0%
15	Person 12	556887	01/12/2020	87.5%
16	Person 13	556998	01/01/2021	87.5%
17	Person 14	557109	01/02/2021	100.0%
18	Person 15	557220	01/03/2021	100.0%
19				61.7%
March '21

Cell Formulas
Range	Formula
E19	E19	=IFERROR(AVERAGE(E4:E18),"")

Testing Sheet.xlsx
	B	C	D	E
22		5 to 10 Minute Test
23		Test Reference	Date of Test	Score
24	Person 1	555667	01/01/2020	0.0%
25	Person 2	555778	01/02/2020	10.0%
26	Person 3	555889	01/03/2020	20.0%
27	Person 4	556000	01/04/2020	30.0%
28	Person 5	556111	01/05/2020	40.0%
29	Person 6	556222	01/06/2020	50.0%
30	Person 7	556333	01/07/2020	60.0%
31	Person 8	556444	01/08/2020	70.0%
32	Person 9	556555	01/09/2020	80.0%
33	Person 10	556666	01/10/2020	90.0%
34	Person 11	556777	01/11/2020	100.0%
35	Person 12	556888	01/12/2020	87.5%
36	Person 13	556999	01/01/2021	87.5%
37	Person 14	557110	01/02/2021	100.0%
38	Person 15	557221	01/03/2021	100.0%
39				61.7%
March '21

Cell Formulas
Range	Formula
B24:B38	B24	=B4
E39	E39	=IFERROR(AVERAGE(E24:E38),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B24:B38,B44:B58,B64:B78	Cell Value	=0	text	NO

Testing Sheet.xlsx
	B	C	D	E
42		Over 20 Minute Test
43		Test Reference	Date of Test	Score
44	Person 1	555668	01/01/2020	0.0%
45	Person 2	555779	01/02/2020	10.0%
46	Person 3	555890	01/03/2020	20.0%
47	Person 4	556001	01/04/2020	30.0%
48	Person 5	556112	01/05/2020	40.0%
49	Person 6	556223	01/06/2020	50.0%
50	Person 7	556334	01/07/2020	60.0%
51	Person 8	556445	01/08/2020	70.0%
52	Person 9	556556	01/09/2020	80.0%
53	Person 10	556667	01/10/2020	90.0%
54	Person 11	556778	01/11/2020	100.0%
55	Person 12	556889	01/12/2020	87.5%
56	Person 13	557000	01/01/2021	87.5%
57	Person 14	557111	01/02/2021	100.0%
58	Person 15	557222	01/03/2021	100.0%
59				61.7%
March '21

Cell Formulas
Range	Formula
B44:B58	B44	=B4
E59	E59	=IFERROR(AVERAGE(E44:E58),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B24:B38,B44:B58,B64:B78	Cell Value	=0	text	NO

Testing Sheet.xlsx
	B	C	D	E
62		Alternative Test
63		Test Reference	Date of Test	Score
64	Person 1	555669	01/01/2020	0.0%
65	Person 2	555780	01/02/2020	10.0%
66	Person 3	555891	01/03/2020	20.0%
67	Person 4	556002	01/04/2020	30.0%
68	Person 5	556113	01/05/2020	40.0%
69	Person 6	556224	01/06/2020	50.0%
70	Person 7	556335	01/07/2020	60.0%
71	Person 8	556446	01/08/2020	70.0%
72	Person 9	556557	01/09/2020	80.0%
73	Person 10	556668	01/10/2020	90.0%
74	Person 11	556779	01/11/2020	100.0%
75	Person 12	556890	01/12/2020	87.5%
76	Person 13	557001	01/01/2021	87.5%
77	Person 14	557112	01/02/2021	100.0%
78	Person 15	557223	01/03/2021	100.0%
79				61.7%
March '21

Cell Formulas
Range	Formula
B64:B78	B64	=B4
E79	E79	=IFERROR(AVERAGE(E64:E78),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B24:B38,B44:B58,B64:B78	Cell Value	=0	text	NO

 

[Saba Sabaratnam]

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

Kind regards

Saba

 

[KMason17]

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]

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