Converting a numeric score to letter grade


Posted by Dan on July 11, 2001 6:49 PM

I have a list of final marks from a test and I want to
convert them to a letter grade acording to a formula (ie.
<50 is "F", >50 and <60 is "P", >60 and < 70 is "C"..etc.)

How do I perform this "if-then-else" task? I would rather
not used a vertical lookup table with 100 rows. Is there
a formula I can use?

Dan.

Posted by Aladin Akyurek on July 12, 2001 12:15 AM

Dan,

It is better to use a formula with VLOOKUP rather than one with IF. Here is how?

=VLOOKUP(A1,{0,"F";50,"P";60,"C"},2,0)

where A1 contains a mark.

Fill in the letter values for 70 and above in the array constant {0,"F";50,"P";60,"C"}. [If you need help on this one, post the conversion table in the follow-up.]

Aladin

==========

Posted by Aladin Akyurek on July 12, 2001 12:18 AM

Correction

The formula must be: =VLOOKUP(A1,{0,"F";50, "P";60, "C"}, 2), that is, without the 4th arg.



Posted by Joe Was on July 12, 2001 7:19 AM

Nested IF(AND(

If your grades are in column E and you put this IF formula in column F it will do all the conversions.

=IF(AND(E5>0,E5<50), "F", IF(AND(E5>=50, E5<60), "P", IF(AND(E5>=60, E5<70), "D", IF(AND(E5>=70, E5<80), "C", IF(AND(E5>=80, E5<90), "B", IF(E5>=90,"A",""))))))


It works I tested it. It also accounts for missing, blank and higher than 100. JSW