Averages

[Steve Moss]

I have a student marking sheet with 2 colums, one is "Individual grade" for
each subject, and the second for "Overall Grade". what i am trying to do is
use the individual grades "A+, A, A-, B, B- etc" to automatically calculate
an Overall grade. for instance Biology, Chemistry and Physics all have
individual grades, but as a collective "Science" they need an average grade.
Can anybody help.

 

[Mike H]

Hi,

Apologies to the original poster I've lost the name associated with this.
This averages grade letters A+ to F-. A couple of points, there must be no
blanks in the range and it's an array formula see array formula instructions
below. If your grades don't go down as far as F- it doesn't matter it will
simply average the grades that are there.

=INDEX({"F-","F","F+","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"},ROUND(AVERAGE(MATCH(A1:A10,{"F-","F","F+","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"},0)),0))


This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Steve Moss]

Many thanks
This works perfectly.

Hi,

Apologies to the original poster I've lost the name associated with this.
This averages grade letters A+ to F-. A couple of points, there must be no
blanks in the range and it's an array formula see array formula instructions
below. If your grades don't go down as far as F- it doesn't matter it will
simply average the grades that are there.

=INDEX({"F-","F","F+","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"},ROUND(AVERAGE(MATCH(A1:A10,{"F-","F","F+","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"},0)),0))


This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike