Average

[Guest]

Does anyone know how one could get an average of a list of say exam marks in
letter form ie; A,B,C,D so if it was a simple short list in a single column
with say 10 letters in total with 6 A's, 2 B's, 1 C and 1 D then the average
i would be looking for would be A though i know that is not a true average
but it is the most recorded mark.

 

[SteveG]

You could use a helper column and convert the letter grades to numbers.
Say if in column A1:A9 you had your grades (a,b,c,d,f). In cell b
enter:

=IF(A1="a",1,IF(A1="b",2,IF(A1="c",3,IF(A1="d",4,IF(A1="f",5)))))

You can then drag this down in your range.

In C1:C5 type 1-5 in ascending order. (1 representing a and so on).

Select cells E1:E5 and type in this formula and commit wit
Ctrl-Shift-Enter:

=FREQUENCY(B1:B9,C1:C5)



Cheers,

Stev

 

[Roger Govier]

Hi Spike

One way
{=CHAR(INT(AVERAGE(CODE(UPPER(A1:A10)))))}

This is an array formula so commit with Ctrl+Shift+Enter not just Enter when
setting up the formula or amending it.
Excel will insert the curly braces { } do not type them yourself.

Regards

Roger Govier

 

[Ron Coderre]

See if this works:

For grades in A1:A10

B1: =SUMPRODUCT(SEARCH(A1:A10,{"FDCBA"})-1)/COUNTA(A1:A10)

Note: that accounts for whole grades only: A,B,C,D,F
Half-grades would need a variation.

Does that help?
Ro