assigning grades

[tracy]

I want to be able to assign a grade in the excel gradebook I'm creating where
if, for example, cell B7 is 99 or greater, cell C7 shows "A+", if B7 is 92 to
98, C7 shows A, 90 to 91, C7 shows A- and on down the line for B+, B, B- etc.
I'd LOVE it if I could learn how to do it!!!

 

[John C]

You need to set up a table, and then do a vlookup.
Make a table, I prefer on a separate tab, such as follows:
Starting in cell A2, through A14, type the following values:
0, 60, 62, 69, 70, 72, 79, 80, 82, 89, 90, 92, 99
NOTE: These are the lower limits of the corresponding grade that you will
type in column B (cells B2 through B14):
Fail, D-, D, D+, C-, C, C+, B-, B, B+, A-, A, A+
Highlight cells A2 through B14, and name the range (you can type the name if
the range in the Name Box just to the left of the formula bar. I used
Tbl_Grade.

Then, on your main worksheet, assuming your numeric grade is in cell B2, and
you want the letter equivalent in column C, you would type the following in
C2, and copy down as needed:
=IF(ISNUMBER(B2),VLOOKUP(Grade,Tbl_Grade,2,TRUE),"")

Essentially, this first checks if there is a number in cell B2: ISNUMBER(B2)
If there is, it will then look at your table, on your separate tab. It will
take the value typed in B2, and find the closest number to it (without going
over), and then retrieve the data (letter grade) in the column next to it.
The TRUE means it is looking for the closest value, if this were false, it
would have to have an exact value, or else would return an error.

Hope this helps.

 

[T. Valko]

Try this...

=LOOKUP(B7,{90;92;99},{"A-";"A";"A+"})

Use the *lower boundary* for each interval and note that the boundary values
must be in ascending order.

90 = lower boundary for A-
92 = lower boundary for A
99 = lower boundary for A+