Need to average collection of years grades into one.

[JNAC]

Hey,

Long time listener, first time caller.

I've been trying to get this to work for me but I've had no luck.

What I'm trying to do is come up with either a set of drop boxes or cells I can enter students grades into that will average them into an overall grade.
The way I figure I would do it is assign each grade (A+ through to E-) a number from 1 to 15 and then use the average of the numbers to come up with an overall grade. The averaging is easy I'm having an issue getting the entered text to represent a number.

I'll attach a spreadsheet with what I'd like it to look like in the end, give or take the key on the left.

Sorry if this is really simple I'm just blank at the moment and can't figure it out Might blame Sunday afternoon for this.

Cheers!

Useless Draft.xlsx

[Ace_XL]

For average score (numeric result)
=SUMPRODUCT(B1:B15*COUNTIF(E1:E15,A1:A15))/COUNTA(E1:E15)

For grade
=INDEX(A1:A15,MATCH(ROUNDUP(SUMPRODUCT(B1:B15*COUNTIF(E1:E15,A1:A15))/COUNTA(E1:E15),0),B1:B15,0))

If score is not an integer, grade would be base don next higher integer

[benishiryo]

welcome to the forum, JNAC. you didnt put in what you are hoping to see as the final result. so let me guess. the total scores for those 5 tests would be 34. this gives you 34:
=SUMPRODUCT(SUMIF(A1:A15,E1:E15,B1:B15))

this gives you an average score using numbers:
=SUMPRODUCT(SUMIF(A1:A15,E1:E15,B1:B15))/COUNTA(E1:E15)

if you wish to use a grading instead, decide if you want a roundup or rounddown.
the lookup wouldn't need any rounding.
=LOOKUP(SUMPRODUCT(SUMIF(A1:A15,E1:E15,B1:B15))/COUNTA(E1:E15),B1:B15,A1:A15)

=LOOKUP(ROUNDUP(SUMPRODUCT(SUMIF(A1:A15,E1:E15,B1:B15))/COUNTA(E1:E15),0),B1:B15,A1:A15)

[JNAC]

Thanks very much everyone for your help, got it working!