Formula to get the average of the top 3 numbers in an array

[sobrien1234]

in column A I have a list of names with up to 8 of the same name. In column B I have numbers which represent times. I want a formula in column that will extract the average of the top 3 numbers for each name.

For example the answers to the formula in column C would be....

NAME	TIME	AVERAGE OF LOWEST 3 TIMES
MEPUNGA DANIEL	-0.05	-0.30
MEPUNGA DANIEL	-0.64	-0.30
MEPUNGA DANIEL	-0.05	-0.30
MEPUNGA DANIEL	-0.19	-0.30
MEPUNGA DANIEL	0.12	-0.30
MEPUNGA DANIEL	0.41	-0.30
IKARUGA BALE	-0.22	-0.11
IKARUGA BALE	-0.02	-0.11
IKARUGA BALE	0.02	-0.11
IKARUGA BALE	-0.03	-0.11
IKARUGA BALE	-0.07	-0.11
IKARUGA BALE	0.40	-0.11
IKARUGA BALE	0.53	-0.11
IKARUGA BALE	0.16	-0.11
IKARUGA BALE	0.00	-0.11
SLINGSHOT TITAN	-0.24	-0.28
SLINGSHOT TITAN	-0.55	-0.28
SLINGSHOT TITAN	0.13	-0.28
SLINGSHOT TITAN	0.06	-0.28
SLINGSHOT TITAN	-0.06	-0.28
SLINGSHOT TITAN	0.06	-0.28
SLINGSHOT TITAN	-0.06	-0.28
SLINGSHOT TITAN	0.17	-0.28

 

[steve the fish]

Try this:

=AVERAGE(SMALL(IF($A$2:$A$24=A2,$B$2:$B$24),{1,2,3}))

 

[Peter_SSs]

You could also employ one of Excel 365's new functions.

20 08 20.xlsm
	A	B	C
1	NAME	TIME	AVERAGE OF LOWEST 3 TIMES
2	MEPUNGA DANIEL	-0.05	-0.29
3	MEPUNGA DANIEL	-0.64	-0.29
4	MEPUNGA DANIEL	-0.05	-0.29
5	MEPUNGA DANIEL	-0.19	-0.29
6	MEPUNGA DANIEL	0.12	-0.29
7	MEPUNGA DANIEL	0.41	-0.29
8	IKARUGA BALE	-0.22	-0.11
9	IKARUGA BALE	-0.02	-0.11
10	IKARUGA BALE	0.02	-0.11
11	IKARUGA BALE	-0.03	-0.11
12	IKARUGA BALE	-0.07	-0.11
13	IKARUGA BALE	0.4	-0.11
14	IKARUGA BALE	0.53	-0.11
15	IKARUGA BALE	0.16	-0.11
16	IKARUGA BALE	0	-0.11
17	SLINGSHOT TITAN	-0.24	-0.28
18	SLINGSHOT TITAN	-0.55	-0.28
19	SLINGSHOT TITAN	0.13	-0.28
20	SLINGSHOT TITAN	0.06	-0.28
21	SLINGSHOT TITAN	-0.06	-0.28
22	SLINGSHOT TITAN	0.06	-0.28
23	SLINGSHOT TITAN	-0.06	-0.28
24	SLINGSHOT TITAN	0.17	-0.28
Average best 3

Cell Formulas
Range	Formula
C2:C24	C2	=AVERAGE(SMALL(FILTER(B$2:B$24,A$2:A$24=A2),{1,2,3}))