Excel grade formula

Question

I'm working on a formula to determine the final grade for a student who completed two examinations and one assignment.

I created a formula that can accomplish the work, but I've run into a snag and I'm not entirely sure why it's returning the wrong answer.

The results for the two examinations and one assignment are A, A+, and B+, respectively, and when they are averaged, should equal 13.66 and a grade of A (rounded upwards). However, when I click outside of the cell, a C+ appears, and I'm not sure why.

=INDEX(L$2:L$16,MATCH(SUMPRODUCT((E8:G8=L$2:L$16)*(M$2:M$16))/COUNTA(E8:G8), M$2:M$16,-1))

Answer 1

Since you said it was just for three scores, the quickest method is to utilize the average of three different lookups, like in:

=AVERAGE(VLOOKUP(E6,gradevalues,2,FALSE),VLOOKUP(F6,gradevalues,2,FALSE),VLOOKUP(G6,gradevalues,2,FALSE))

...where grade values is a named range representing the list of letter grades-to-scores.

eg.,