Return A, B, C, or D depending on percentage

[lawrend]

I am trying to write a formulae that returns an A, B, C or D depending on
percentage scored in a test.
i.e i will have students who do a test, then receive a percentage mark out
of 100.
Then i would like to convert that to a grade as below.

Say 0 - 50 = D
51 - 69 = C
70 - 85 = B
86 - 100 = A

Can anyone help please. I have succeded in doing this in a lookup table but
I would like to be able to write this in a single cell as a formulae. I have
tried doing it with an IF statement but can't seem to get my head around it.

Thanks in advance ....

 

[Frank Kabel]

Hi
put your lookup table in a separate table (e.g. called lookup):
- in column A enter ONLY the lower boundary (e.g. 0 in A1, 51 in A2,
etc.)
- in column B enter the letter

Now use the following formula on your other sheet (where A1 contains
the lookup value):
=VLOOKUP(A1,'lookup'!A1:B10,2,TRUE)

 

[Myrna Larson]

In your IF formula, you are probably testing the values from bottom to top
instead of top to bottom.

=IF(A1>=86,"A",IF(A1>=70,"B",IF(A1>=51,"C","D")))

PS: IMO a lookup formula is more efficient. If you later change the cutoff
points, you need only to change the data in the table.

 

[Trevor Shuttleworth]

One way:

=IF(A1<51,"D",IF(A1<70,"C",IF(A1<86,"B",IF(A1<101,"A","error"))))

This tests the value in cell A1 and returns a grade from D to A. If a
student scores more than 100, it is marked as an error ... though you don't
need this test.

Regards

Trevor

 

[Aladin Akyurek]

If values as 69.4 and 69.7 are not possible...

=LOOKUP(A1,{0,"D";51,"C";70,"B";86,"A"})

 

[Gord Dibben]

And yet another.........

=LOOKUP(A1,{0,51,70,86},{"D","C","B","A"})

Note the curly braces internally.

Gord Dibben Excel MVP

 

[lawrend]

Many thanks, much appreciated

lawrend

 

[Mike Ferguson]

I use a formula that does basically the same thing, converting point
totals into grades.
For example, if Column D contains total points (or percentages, as
shown below), I use this formula for Column E:

=IF(D1>90,"A",IF(D1>80,"B",IF(D1>70,"C",IF(D1>60,"D","F"))))

I hope this helps.

-JMF