Averaging letters

[Dale]

I am trying to create a teacher's markbook for my A-level coursework.
One of the things I am trying to get is an average of a series of
letter.

So for example, if I have a column of letters - A, B, C, D, E - the
average would be C.

I have searched the groups already and found a thread discussing this
including some solutions, but none of them work for me as they seemed
to be Macintosh specific. I am using the Windows Excel 2000.

Any help appreciated. Many thanks.

 

[Jerry W. Lewis]

Your best bet is to convert to numbers and average them. I don't know
what numerical value to assign an E, but on the 4-point scale (A=4,...,
D=1,F=0) that was in use when I was a student and later a college
teacher, you could use

=AVERAGE(IF(range="A",4,IF(range="B",3,IF(range="C",2,IF(range="D",1,IF(range="F",0))))))

array entered (Ctrl-Shift-Enter) to get the grade point average.

Jerry

 

[Dale]

Thanks very much, now I'm getting somewhere!

If possible, I'd the average to come back as a letter. How would I
modify the formula to do that?

By the way, you were nearly right with the grade scheme, just the
other way round and with an E;

A = 1
B = 2
C = 3
D = 4
E = 5

So the formula I'm using in my worksheet is like this:

=AVERAGE(IF(C8:C38="A",1,IF(C8:C38="B",2,IF(C8:C38="C",3,IF(C8:C38="D",4,IF(C8:C38="E",5))))))

 

[Jerry W. Lewis]

You will need to use a second cell, since Excel can nest no more than 7
function calls.

How do you want to handle fractions?
=CHAR(ROUND(gpa,0)+64)
may do what you want.

Jerry

 

[Jerry W. Lewis]

And in that vein, you could use
CODE(A1)-64
to convert your letters to numbers and thus avoid the nested IF values.
If you do so, I would suggest using a Data|Validation formula to
ensure that nothing other than A, B, C, D,or E is inadvertantly entered.

Jerry

 

[Asmodeus]

(e-mail address removed) (Dale) wrote in @posting.google.com:

Thanks very much, now I'm getting somewhere!

You can't nest IFs with pluses and minuses. Use a vlookup
instead, something like:

Letter GPA
A 4
A- 3.7
B+ 3.3

etc.

Use a vlookup function to convert the letter to a number,
then convert the average number back to a letter.