Averaging letter grades


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
 
Ad

Advertisements

J

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
 
J

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
 
L

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.
 
J

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
 
L

Leo Heuser

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.

Thanks for the kind words, Jim!

Regards
LeoH
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top