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.
--
with kind regards

Spike

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 b1
enter:

=IF(A1="a",1,IF(A1="b",2,IF(A1="c",3,IF(A1="d",4,I F(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 with
Ctrl-Shift-Enter:

=FREQUENCY(B1:B9,C1:C5)



Cheers,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=487342

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


Spike wrote:
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.

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?
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=487342