How to get the average using the best x scores from a list of y performances

[Ratfink52]

In a similar fashion of calculating a golf handicap by using the say the best 8 scores from your last 10, I need to do this for a database of performances for another sport where each player has a different number of games in the same period. So I want to drop the say two worst performance from the last x number of games regardless of the length of time elapsed from their first game to the last. The database is typically date (of game), name, score. From this I extract a pivot table of the average scores, but want the average to be the average of the best x scores from the most recent y games.

Help please!

 

[hagia_sofia]

Hello, based on your description it could be something like this but we would need to see the relevant data:

=AVERAGE(TAKE(CHOOSECOLS(SORT(FILTER(A2:C21,B2:B21="Xavier")),3),-8))

 

[Peter_SSs]

Welcome to the MrExcel board!

Could it be this?

Options in E2 for last x scores to consider and F2 for how many "worst" scores to drop off.
In my example,
If a player has 5 or more scores then the last 5 are taken and the worst 2 dropped before averaging.
If a player only has 4 scores then only 1 would be dropped to average the best 3.
Similarly if a player only had 3 scores then none would be dropped and all 3 averaged.
If a player has less than 3 scores then all their scores are averaged.

Ratfink52.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	Player	Score		Last x	Drop n		Name	Avg best 3 of last 5
2	1/07/2024	Player 1	55		5	2		Player 1	79
3	1/07/2024	Player 2	82					Player 2	80.66666667
4	1/07/2024	Player 3	68					Player 3	96.66666667
5	1/07/2024	Player 7	56					Player 4	91.66666667
6	1/07/2024	Player 9	57					Player 5	86
7	1/07/2024	Player 5	61					Player 6	88
8	1/07/2024	Player 8	74					Player 7	83.33333333
9	2/07/2024	Player 3	51					Player 8	80.66666667
10	2/07/2024	Player 4	99					Player 9	57
11	2/07/2024	Player 8	72
12	2/07/2024	Player 2	63
13	2/07/2024	Player 5	99
14	2/07/2024	Player 1	87
15	2/07/2024	Player 7	50
16	3/07/2024	Player 4	65
17	3/07/2024	Player 1	83
18	3/07/2024	Player 2	73
19	3/07/2024	Player 7	75
20	3/07/2024	Player 5	54
21	3/07/2024	Player 3	90
22	3/07/2024	Player 8	68
23	4/07/2024	Player 3	97
24	4/07/2024	Player 4	95
25	4/07/2024	Player 7	88
26	4/07/2024	Player 6	89
27	4/07/2024	Player 8	96
28	4/07/2024	Player 1	61
29	4/07/2024	Player 2	82
30	5/07/2024	Player 7	87
31	5/07/2024	Player 1	67
32	5/07/2024	Player 5	98
33	5/07/2024	Player 4	81
34	5/07/2024	Player 6	87
35	5/07/2024	Player 2	87
36	5/07/2024	Player 3	98
37	6/07/2024	Player 3	95
38	6/07/2024	Player 4	68
39	6/07/2024	Player 1	62
40	6/07/2024	Player 2	63
41	7/07/2024	Player 1	58
42	7/07/2024	Player 4	85
43	7/07/2024	Player 7	76
44	8/07/2024	Player 4	96
45	8/07/2024	Player 3	91
46	8/07/2024	Player 1	65
Sheet1

Cell Formulas
Range	Formula
I1	I1	="Avg best "&E2-F2&" of last "&E2
H2:I10	H2	=LET(n,SORT(UNIQUE(B2:B40)),HSTACK(n,BYROW(n,LAMBDA(r,AVERAGE(TAKE(SORT(TAKE(FILTER(R1:R39,Q1:Q39=r),E2),,-1),E2-F2))))))
Dynamic array formulas.