Increasing a range of grades available

[confused teacher]

Hi

I am trying to get a formula to work that will assign a grade to a cell
based on a numeric result. As as an example if a student scores anything
above 84.99 (grade recorded in Cell D8) the grade of HD is assigned by the
formula listed below.

=IF(D8>84.99,"HD",IF(D8>74.99,"D",IF(D8>64.99,"C",IF(D8>49.99,"P",IF(D8>42.99,"PC",IF(D8>=0.1,"F"))))))

I need to be able to split these broad grades into upper and lower ranges,
for instance a student who scores 96 will get a grade of HD+ but the formula
above doesn't recognise this. Similarly a student might get 82 which equates
to a D+.

I need a formula that will lookup a grade in cell D8 and assign a letter
grade based on the following:
HD+ = >95; HD = >=90; HD = >=85; D+ = >=82; D = >=80; D- = >=75; C+ = >=72;
C = >=70; C- = >=65; P+ = >=60; P = >=55; P- = >=50; PC = >=43; F = <=42

Any ideas would be greatly appreciated

Thanks Brian

 

[Biff]

Hi!

Create a 2 column table:

............A............B
1.........0............F
2........43...........PC
3........50...........P-
4........55...........P
5........60...........P+
6........65..........C-
...
13......95..........HD+

Then use this formula:

=IF(D8="","",VLOOKUP(D8,A1:B13,2))

Biff

 

[MartinW]

Hi confused teacher,

One way would be to put the numbers 1 to 100 in column A
and in column B put the corresponding grade then use a
VLOOKUP formula to access it.
You could hide the lookup table or put it in a separate worksheet.

HTH
Martin

 

[confused teacher]

Thanks, This is a great help.

Brian

Hi!

Create a 2 column table:

............A............B
1.........0............F
2........43...........PC
3........50...........P-
4........55...........P
5........60...........P+
6........65..........C-
...
13......95..........HD+

Then use this formula:

=IF(D8="","",VLOOKUP(D8,A1:B13,2))

Biff

 

[Biff]

You're welcome!

Biff

Thanks, This is a great help.

Brian