MrExcel Publishing
Your One Stop for Excel Tips & Solutions

grade percent to letter grade problem


Posted by Erik on November 08, 2001 10:04 AM

I am trying to help my teacher with her excel spreadsheet but I'm pretty new at this. All of the students grades are based on their test scores averaged togther (we got that formula down). What we would like to do is have a formula that gives those averaged test scores a letter grade automatically. So if a student got a 68%, excel would show up with a "D". But if they got a 87%, excel would show up with a "B" and so on. I have an IF calculation that gives me a letter if a number is over a certian size, but I cant get a (if D1>59 then "D", if D1>69 then "C", if D1>79 then "B", if D1>89 then "A") kinda formula. Thats what I need. Thank you guys


Posted by bob Umlas on November 08, 2001 10:12 AM

You're going about it backwards. Try:
=IF(D1>89,"A",IF(D1>79,"B",IF(D1>69,"C",IF(D1>59,"D","F"))))
By testing if D1>59 first, then 72 would be true as would 100!

Posted by IML on November 08, 2001 10:12 AM

If your grade is in cell A1, you could use
=VLOOKUP(A1,{0,"f";60,"d";70,"c";80,"b";90,"a"},2)

Posted by Aladin Akyurek on November 08, 2001 10:18 AM

Erik,

Lets suppose this to be your conversion table:

{0,"F";
60,"D";
70,"C-";
75,"C";
77,"C+";
80,"B-";
85,"B";
87,"B+";
90,"A-";
94,"A";
98,"A+"}

You might post your real table if necessary.

Lets also suppose that you have a score in A1,

in A2 enter: =VLOKKUP(A1,{0,"F";60,"D";70,"C-";75,"C";77,"C+";80,"B-";85,"B";87,"B+";90,"A-";94,"A";98,"A+"},2,0)

It'll give what you want.

You can also have this table in some sheet, give it a name, and use that name in the above formula.

In order to that:

Select all of the cells of this table (don't include any labels in the selection);
Go the Name Box on the Formula Bar and type e.g., LETTERGRADES followed by ENTER.

Chan ge the above formula to:

=VLOKKUP(A1,LETTERGRADES,2,0)

Aladin

========

Posted by Daniel on November 08, 2001 10:20 AM

I can help you as im doing this for an A Level project
Im willing to help you but i have to ask are you
really just helping a teacher or is it for your
project - ill still help im just interested to know
who else is doing this project.

Heres what you do.

You have to create a named list of cells that
you can then lookup the percent score against and
have the result put in your grade cell.

Type the percent values in cells A1 to A6 (e.g. 69
for the C grade, 75 for the B, etc. in cells B1
to B6 enter the correspoding grades with the
lowest grade at the top going down. then select A1
to B6 and in the cell display box to the left of
the formula box type in "GradeBoundaries" (without the
quotes)and hit enter. Then check that this range
is named by picking GradeBoundaries from the drop-down box
It should select these cells.

Ok the next stage is to enter in the cell where
you want to display the formula the following:

=LOOKUP(V7,GradeBoundaries)

V7 is the cell where the percentage being looked
up agaist your list of grade boundaries is.
You can change it to the cell where your % is stored

If you need any more help just e-mail me (see above)

Posted by Aladin Akyurek on November 08, 2001 10:21 AM


Posted by Erik on November 08, 2001 11:29 AM

Woohoo thanks!

Hey that works great, thanks alot, I didnt think about going the other way....