Grade entry

[Mister Big Shot]

i cannot get a simple formula to work and would appreciate any help

i would like a column that tabulates my grades

eg. if column 1 is >=95 column 2 will read A+
if column 1 is >=90 and <95 column 2 will show A

and so on....(I should be able to get the rest once i get this info)

thanks for any help
MBS

[Stuart Farr]

Hi MBS,

You could set up a table with the marks in one column (0-100) and place the grade alongside each value. You can then use vlookup() to interrogate the table and return the appropriate grade.

Stu

[Mister Big Shot]

however i have a problem when I have grades that are decimal values.

Is there not a way to enter this as a formula? without having to set up a new column. ...using 'IF'???

[Stuart Farr]

Yes, you can nest up to 7 "if's". So provided your grade range does not exceed 7, it can be done. If you're not sure how, give me the ranges and I'll review.

[davesexcel]

if you used vlookup such as

=vlookup(a1,b2:c100,2,true)

that should find the closest value

[Stuart Farr]

Thanks Dave, I missed the blindingly obvious!!

[Leith Ross]

Hello Mr Big Shot,

Given the limitations you have run into, I feel your best solution is a UDF (User Defined Function). This will return a letter grade for a any cell with a decimal value. You will need to copy this code, insert a Standard VBA Module, and paste the code into. You can then use this macro just like a standard Excel formula.

Public Function LetterGrade(Number_Grade) As String

  Application.Volatile
  
  Dim L As String
  Dim N As Single

    N = CSng(Number_Grade)

    If N >= 95 Then L = "A+": GoTo Finish
    If N >= 90 And N < 95 Then L = "A": GoTo Finish
    If N >= 85 And N < 90 Then L = "B+": GoTo Finish
    If N >= 80 And N < 85 Then L = "B": GoTo Finish
    If N >= 75 And N < 80 Then L = "C+": GoTo Finish
    If N >= 70 And N < 75 Then L = "C": GoTo Finish
    If N >= 65 And N < 70 Then L = "D+": GoTo Finish
    If N >= 60 And N < 65 Then L = "D": GoTo Finish
    If N < 60 Then L = "F"
    
Finish:
   LetterGrade = L

End Function

Example of use:

Say cell A1 = "77.255"
Cell B1 formula is: =LetterGrade(A1)
The result in B1 will C+

Sincerely,
Leith Ross

[Mister Big Shot]

the ranges would be as follows

A= >=85-100%
B= >=75- <85
C= >=65- <75
D= >=55- <65
F= <55

[Paul]

For that, couldn't you simply use the formula:

=LOOKUP(A1,{0,55,65,75,85},{"F","D","C","B","A"})

Where A1 (or any other cell) contains the grade (with or without decimals)?

If A1 were 54.999, you would see F. If it were 77.22, you would see B.

You can adjust the two lookup/result arrays to fit your data (if you have A+, A, A-, B+, etc..)

[Stuart Farr]

Hi MBS,

The formula would look like this:
=IF(A3>=85%,"A",IF(A3>=75%,"B",IF(A3>=65%,"C",IF(A3>=55%,"D","F"))))
assuming the percentage is in cell A3.

Stu

[Mister Big Shot]

good stuff.

thanks