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

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 ....

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)


--
Regards
Frank Kabel
Frankfurt, Germany


lawrend wrote:
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 ....

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.


On Sat, 13 Nov 2004 23:14:18 +0000 (UTC), "lawrend"
wrote:

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 ....

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


"lawrend" wrote in message
...
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 ....

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

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

lawrend Wrote:
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 ....


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=278042

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

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

Note the curly braces internally.

Gord Dibben Excel MVP


On Sat, 13 Nov 2004 17:43:30 -0600, Aladin Akyurek
wrote:


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

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

lawrend Wrote:
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 ....

Many thanks, much appreciated

lawrend

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(D190,"A",IF(D180,"B",IF(D170,"C",IF(D160," D","F"))))

I hope this helps.

-JMF