Averaging letter grades

[Dr. Indera]

hello,

i'd like to know if it is possible to write a formula that will average 16
letter grades.
in column b, from row 6 to 21 is where i enter the letter grade for each
assignment.
i'd like to have the average of these grades in g23.

any help would be greatly appreciated.

thank you
indera

 

[Jason Morin]

In order to average, you must assign a numerical value. If
there are 5 possible grades (A,B,C,D,F) and their
respective numerical equivalents are 5,4,3,2,1, then you
could use something like:

=CHOOSE(ROUND(AVERAGE(70-CODE
(B6:B21)),0),"F","D","C","B","A")

Array-entered (meaning press ctrl/shift/enter).

If your grading scale include + and - , then it'll require
a different and probably bigger formula.

HTH
Jason
Atlanta, GA

 

[Jason Morin]

Oops...after further testing, I realized F was being
assigned the wrong value. Use this instead:

=CHOOSE(ROUND(AVERAGE(70-CODE(IF
(B6:B21="F","E",B6:B21))),0),"F","D","C","B","A")

Still array-entered.

HTH
Jason
Atlanta, GA

 

[Leo Heuser]

Hello Indera

If "+" and "-" are included, here's one
way to do it. Remove and add elements
if necessary. All numbers are rounded to
nearest integer. E.g. an average of 7.8125
will round to 8, which is C.
(F- is 1 and A+ is 15)

In G23 enter:

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

The formula is an array formula, and
must be entered with <Shift><Ctrl><Enter>
also if edited later.

 

[Jim Cone]

Leo,

No, I am not Indera, but I wanted to say: Very impressive solution.
(I need to get my head out of the code modules more often.)
Please keep your contributions coming.

Regards,
Jim Cone
San Francisco, CA

 

[Leo Heuser]

Leo,

No, I am not Indera, but I wanted to say: Very impressive solution.
(I need to get my head out of the code modules more often.)
Please keep your contributions coming.

Thanks for the kind words, Jim!

Regards
LeoH

 

[Katie Mc]

Any idea how to modify this formula to not include empty cells in the calculation?