Averaging letter grades

  • Thread starter Thread starter Dr. Indera
  • Start date Start date
D

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
 
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
 
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
 
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.
 
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
 
Jim Cone said:
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.
Click to expand...

Thanks for the kind words, Jim!

Regards
LeoH
 
Back
Top