Formula to work out the average value of the most recent entries v2

[Jonny_71]

Hi,

In the example above, in cell F26 if I wanted to calculate the average number of goals scored by Team A in the preceding three weeks what would the formula be?

So the answer would be 0.67 (average of 1 + 1 + 0) for the previous three weeks. I've tried but I just can't get it right.

Thank you.

Also asked here Formula to calculate average of most recent values

 

[Toadstool]

Hi Jonny_71,

I changed the date format as I'm using a US PC.
You don't define "for the previous three weeks" so I'm assuming it's 3 weeks before the last date shown (cell G2).
The answer you seek is in cell I2 which uses SUMIFS to add all Home and Away scores for the Team in H2 from the calculated date by the COUNTIF the team in H2 appears from the specified date in Home or Away columns.

Jonny_71.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	Home	Away	Score	Score		Date	Team	Answer
2	01-Jan-20	Team A	Team F	1	1		09-Jan-20	Team A	0.666667
3	01-Jan-20	Team B	Team G	4	1
4	01-Jan-20	Team C	Team H	1	0
5	01-Jan-20	Team D	Team I	2	0
6	01-Jan-20	Team E	Team J	1	0
7
8	08-Jan-20	Team F	Team A	0	1
9	08-Jan-20	Team G	Team B	1	1
10	08-Jan-20	Team H	Team C	2	1
11	08-Jan-20	Team I	Team D	2	3
12	08-Jan-20	Team J	Team E	0	0
13
14	15-Jan-20	Team A	Team G	1	4
15	15-Jan-20	Team B	Team H	0	3
16	15-Jan-20	Team C	Team I	1	0
17	15-Jan-20	Team D	Team J	2	0
18	15-Jan-20	Team E	Team F	3	2
19
20	22-Jan-20	Team F	Team B	2	0
21	22-Jan-20	Team G	Team C	1	2
22	22-Jan-20	Team H	Team D	1	3
23	22-Jan-20	Team I	Team E	0	2
24	22-Jan-20	Team J	Team A	4	0
25
26	29-Jan-20	Team A	Team H	1	2
27	29-Jan-20	Team B	Team I	2	0
28	29-Jan-20	Team C	Team J	1	0
29	29-Jan-20	Team D	Team F	2	2
30	29-Jan-20	Team E	Team G	0	3
Sheet1

Cell Formulas
Range	Formula
G2	G2	=MAX(A:A)-20
I2	I2	=(SUMIFS(D:D,A:A,">="&$G$2,B:B,$H$2)+SUMIFS(E:E,A:A,">="&$G$2,C:C,$H$2))/(COUNTIFS(A:A,">="&$G$2,B:B,$H$2)+COUNTIFS(A:A,">="&$G$2,C:C,$H$2))

 

[Jonny_71]

Hi Jonny_71,

I changed the date format as I'm using a US PC.
You don't define "for the previous three weeks" so I'm assuming it's 3 weeks before the last date shown (cell G2).
The answer you seek is in cell I2 which uses SUMIFS to add all Home and Away scores for the Team in H2 from the calculated date by the COUNTIF the team in H2 appears from the specified date in Home or Away columns.

Jonny_71.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	Home	Away	Score	Score		Date	Team	Answer
2	01-Jan-20	Team A	Team F	1	1		09-Jan-20	Team A	0.666667
3	01-Jan-20	Team B	Team G	4	1
4	01-Jan-20	Team C	Team H	1	0
5	01-Jan-20	Team D	Team I	2	0
6	01-Jan-20	Team E	Team J	1	0
7
8	08-Jan-20	Team F	Team A	0	1
9	08-Jan-20	Team G	Team B	1	1
10	08-Jan-20	Team H	Team C	2	1
11	08-Jan-20	Team I	Team D	2	3
12	08-Jan-20	Team J	Team E	0	0
13
14	15-Jan-20	Team A	Team G	1	4
15	15-Jan-20	Team B	Team H	0	3
16	15-Jan-20	Team C	Team I	1	0
17	15-Jan-20	Team D	Team J	2	0
18	15-Jan-20	Team E	Team F	3	2
19
20	22-Jan-20	Team F	Team B	2	0
21	22-Jan-20	Team G	Team C	1	2
22	22-Jan-20	Team H	Team D	1	3
23	22-Jan-20	Team I	Team E	0	2
24	22-Jan-20	Team J	Team A	4	0
25
26	29-Jan-20	Team A	Team H	1	2
27	29-Jan-20	Team B	Team I	2	0
28	29-Jan-20	Team C	Team J	1	0
29	29-Jan-20	Team D	Team F	2	2
30	29-Jan-20	Team E	Team G	0	3
Sheet1

Cell Formulas
Range	Formula
G2	G2	=MAX(A:A)-20
I2	I2	=(SUMIFS(D:D,A:A,">="&$G$2,B:B,$H$2)+SUMIFS(E:E,A:A,">="&$G$2,C:C,$H$2))/(COUNTIFS(A:A,">="&$G$2,B:B,$H$2)+COUNTIFS(A:A,">="&$G$2,C:C,$H$2))

That's so helpful, thank you Toadstool.

 

[Peter_SSs]

If you have the FILTER function in your Excel 365 then you could also try this

20 11 15.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	Home	Away	Score	Score			Team	Answer
2	1/01/2020	Team A	Team F	1	1			Team A	0.666666667
3	1/01/2020	Team B	Team G	4	1
4	1/01/2020	Team C	Team H	1	0
5	1/01/2020	Team D	Team I	2	0
6	1/01/2020	Team E	Team J	1	0
7
8	8/01/2020	Team F	Team A	0	1
9	8/01/2020	Team G	Team B	1	1
10	8/01/2020	Team H	Team C	2	1
11	8/01/2020	Team I	Team D	2	3
12	8/01/2020	Team J	Team E	0	0
13
14	15/01/2020	Team A	Team G	1	4
15	15/01/2020	Team B	Team H	0	3
16	15/01/2020	Team C	Team I	1	0
17	15/01/2020	Team D	Team J	2	0
18	15/01/2020	Team E	Team F	3	2
19
20	22/01/2020	Team F	Team B	2	0
21	22/01/2020	Team G	Team C	1	2
22	22/01/2020	Team H	Team D	1	3
23	22/01/2020	Team I	Team E	0	2
24	22/01/2020	Team J	Team A	4	0
25
26	29/01/2020	Team A	Team H	1	2
27	29/01/2020	Team B	Team I	2	0
28	29/01/2020	Team C	Team J	1	0
29	29/01/2020	Team D	Team F	2	2
30	29/01/2020	Team E	Team G	0	3
31
32
Average

Cell Formulas
Range	Formula
I2	I2	=AVERAGE(FILTER(IF(B2:B300=H2,D2:D300,IF(C2:C300=H2,E2:E300)),A2:A300>MAX(A2:A300)-21))

 

[Jonny_71]

If you have the FILTER function in your Excel 365 then you could also try this

20 11 15.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	Home	Away	Score	Score			Team	Answer
2	1/01/2020	Team A	Team F	1	1			Team A	0.666666667
3	1/01/2020	Team B	Team G	4	1
4	1/01/2020	Team C	Team H	1	0
5	1/01/2020	Team D	Team I	2	0
6	1/01/2020	Team E	Team J	1	0
7
8	8/01/2020	Team F	Team A	0	1
9	8/01/2020	Team G	Team B	1	1
10	8/01/2020	Team H	Team C	2	1
11	8/01/2020	Team I	Team D	2	3
12	8/01/2020	Team J	Team E	0	0
13
14	15/01/2020	Team A	Team G	1	4
15	15/01/2020	Team B	Team H	0	3
16	15/01/2020	Team C	Team I	1	0
17	15/01/2020	Team D	Team J	2	0
18	15/01/2020	Team E	Team F	3	2
19
20	22/01/2020	Team F	Team B	2	0
21	22/01/2020	Team G	Team C	1	2
22	22/01/2020	Team H	Team D	1	3
23	22/01/2020	Team I	Team E	0	2
24	22/01/2020	Team J	Team A	4	0
25
26	29/01/2020	Team A	Team H	1	2
27	29/01/2020	Team B	Team I	2	0
28	29/01/2020	Team C	Team J	1	0
29	29/01/2020	Team D	Team F	2	2
30	29/01/2020	Team E	Team G	0	3
31
32
Average

Cell Formulas
Range	Formula
I2	I2	=AVERAGE(FILTER(IF(B2:B300=H2,D2:D300,IF(C2:C300=H2,E2:E300)),A2:A300>MAX(A2:A300)-21))

That's great. Thank you for your help, Peter.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.