Average Grade for Letters

[Guest]

I have a column of values which are all grades A to U and I would like to
work out the Average of that column but can only figure out how to average
numbers. Is there anyway of averaging a column of letter values?

 

[Roger Govier]

Hi

Try the array entered formula
{=CHAR(ROUND(AVERAGE(CODE(A1:A100)),0))}

To commit or Edit an array formula type Ctrl+Shift+Enter (CSE) not just
Enter.
When you use CSE, Excel will insert curly braces { } around the
formula. Do not type them yourself.

 

[David Biddulph]

=CHAR(AVERAGE(CODE(A1:A9))) entered as an array formula (Control Shift
Enter).

 

[Guest]

Andreah,

Averaging of the ASCII codes for the letters A to U give a biased result
towards U grades (presumably U means un-graded) because it has an ASCII code
of 117 campared for A to F which have codes in the range 97 to 102. As an
example 1 U grade and 8 A grades gives and average of C but 1 F grade and 8 A
gives an average of A.

To get an anawer that represents reality I think what you need to do is
assign values to grades (not ASCII codes) and average these.

An excellent explanation can be found here, complete with example workbook.

http://www.ucalgary.ca/~rzach/teaching/grades.html

Mike