D

#### Dr. Indera

hello,

i'd like to know if it is possible to write a formula that will average 16
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

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

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

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?