MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Changing a Number grade to a letter Grade...


Posted by Tinamarie Stone on November 27, 2001 5:25 AM

Hi everyone,

I am taking Excel 2000 in college, and I have a test on the 12th
of December. This test includes a bonus question where you have
to know how to change a number grade to a letter grade. Does any
one have an easy-to-understand answer on going about this? I would
really appreciate it.


Posted by Juan Pablo G. on November 27, 2001 5:31 AM

This question has appear quite a few times. I'm posting the 'accepted' formula to do this:

=VLOOKUP(A1,{0,"F";59.5,"D";69.5,"C";79.5,"B";89.5,"A"},2)

Where the number grade is in A1.

Juan Pablo G.

Posted by JJ on November 27, 2001 5:50 AM

I think I understand what you want!

=if(a2>=70,"Grade a",if(a2>=60,"Grade b",if(a2>=50,"Grade c","Fail")

This Formula uses Conditional Logic i.e. testing a cell with criteria such if Tinamarie gets 60 (and that mark is in Cell A2) then because it is Greater Than or Equal to 60 the outcome "Grade b" is returned!

The reason u start with the highest value 1st is because if u put the criteria a2>=50 1st excel would return the outcome "Grade c"...

With these formulas its best to read them back to yourself several times: "if cell a2 is greater than or equal to 70 then return the outcome "grade a", if not move on to the second criteria which is if a2 is greater than 60 then return a value of "grade b", if not then move onto the next one if a2 is greater than or equal to 50 then return "grade c", otherwise return "fail".

Posted by JJ on November 27, 2001 6:07 AM

Its always interesting seeing different methods!

I've had another go using VLOOKUPs aswell...

=VLOOKUP(A2,grades,2)

...well the same thing but i've named the table range - grades!

0 f
59.5 d
69.5 c
79.5 b
89.5 a

Tinamarie, if u want an explanation of vlookup then say so, but might be simpler if u have not used vlookup's b4 to play with conditional logic 1st!