Allocate #'s to Letter Grades!

[kgree367]

Hi. I am a teacher.
For my excel markbook I want a way I can type in student grade via letter and then excel do an AutoSum of all those letters to give me their overall grade.

We have a # system we use.
A+ = 13
A = 12
A- = 11
B+ = 10
B= 9
B-= 8
C+= 7
C= 6
C-= 5
D+= 4
D= 3
D-= 2
E= 1
N= 0

If a kid gets 2B's 2C's and a D. We add these together (9+9+6+6+3) and divide by the number of criteria (9+9+6+6+3/ 5) to gather an average score then transform it back to a letter grade (6.6 so round to 7 = C+) .

Is this possible?? Every way I have tried it doesn't like the +'s and -'s.

Hope it makes sense what I want to achieve.

[AliGW]

Welcome to the forum.

Is it Excel 2016 that you have?

I would do this (the sample workbook is from an earlier thread):

=INDEX(_Reports[Grade],MATCH(ROUND(AVERAGE(IF(D4:F4=_Reports[Grade],_Reports[Score])),0),_Reports[Score],0))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

NOTE: The formula in the workbook uses ROUNDDOWN, but I have used ROUND above. There's also the option of ROUNDUP, so choose whichever gives the result you want.