Finding the average of the top 10 values

[CoogansBluff]

Column A has students' names for each time a student took a test.
Column B has their grades on the tests.
Some students took more tests than others, so the number of times a student's name appears in column A could vary from 1 to 10.
In column C, I'll have every student's name listed once.
In column D, I want a formula that would compute the students' final grade based on the following criteria - average of their best 5 test scores, but if less than 5 tests taken, it would be the average of however many tests they took. Take one test, make a 92, and that's your final grade.

What formula would I need for cell D1? One that I could copy and post down that column to determine the final grade for all students?

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1	Name	Grade	Name	Top 5 Average	Number of tests included
2	Amy	79	Amy	88.4	5
3	Bob	77	Bob	79.8	5
4	Bob	77	Chris	91.6	5
5	Bob	69	Dan	87	2
6	Chris	73	Em	62	1
7	Dan	75
8	Amy	100
9	Bob	61
10	Chris	95
11	Bob	63
12	Chris	88
13	Em	62
14	Chris	87
15	Chris	93
16	Dan	99
17	Amy	80
18	Amy	95
19	Chris	62
20	Bob	93
21	Bob	82
22	Chris	95
23	Bob	70
24	Amy	88
25	Chris	70
26	Chris	84
27
Sheet9

Cell Formulas
Range	Formula
D2:D6	D2	=AVERAGE(LARGE(IF(A$2:A$50=C2,B$2:B$50),ROW(INDIRECT("1:"&MIN(5,COUNTIF(A$2:A$50,C2))))))
E2:E6	E2	=MIN(5,COUNTIF(A$2:A$50,C2))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[CoogansBluff]

Brilliant, thanks! Really appreciate folks like yourself who spend time to actually create a spreadsheet with the solution. That made it so easy from my end. Can't thank you enough!

 

[Eric W]

Happy to help!