Grading - Marks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Adel Handal - 19 Apr 2007 10:37 GMT

Hi,
I have more than one worksheet containing marks that result from certain
calculations.
I need to change them from numbers to A, B, C according to the following:
mark is between 95 100 gives A+
90 to 94 gives A
85 to 89 gives B+
80 to 84 gives B

gwoing down until:
50 to 54 gives E
less than 50 gives F
What possible way is there for acheiving this task.

Reply to this Message

JE McGimpsey - 19 Apr 2007 10:53 GMT

one way:

=LOOKUP(A1,{49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";
75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

> Hi,
> I have more than one worksheet containing marks that result from certain
[quoted text clipped - 9 lines]
> less than 50 gives F
> What possible way is there for acheiving this task.

Reply to this Message

Adel Handal - 19 Apr 2007 11:55 GMT

The cell already has a formula for calculating the average! I cannot use
your formula in the same cell!
Do I have to make another sheet for that and use first sheet for marks and
the second sheet for the letters A, B, C,...

> one way:
>
[quoted text clipped - 14 lines]
>> less than 50 gives F
>> What possible way is there for acheiving this task.

Reply to this Message

JE McGimpsey - 19 Apr 2007 14:19 GMT

No, just substitute your existing formula for the A1, below.

> The cell already has a formula for calculating the average! I cannot use
> your formula in the same cell!
[quoted text clipped - 5 lines]
> > =LOOKUP(A1,{49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";
> > 75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Reply to this Message

Adel Handal - 19 Apr 2007 12:09 GMT

Hi,
To make it more clear; Cell E21 in the sheet "certificates" has the
following formula:
=IF(VLOOKUP($A$3,Marks!$B$14:$DO$83,7,FALSE)<>"",(VLOOKUP($A$3,Marks!$B$14:$DO$83,7,FALSE)),"")
The result is the number 57.
What changes should be done to see E+ instead of the number.
I don't mind if I have to create a new sheet with the same design depending
on values in the sheet "Marks" to get what I want.

i hope I made it clear enough.

> one way:
>
[quoted text clipped - 14 lines]
>> less than 50 gives F
>> What possible way is there for acheiving this task.

Reply to this Message

Nico - 19 Apr 2007 13:02 GMT

Hi,
I doubt very much such a thing is possible.
Most probably the best idea is to make another sheet based on this, where
you apply the formula.
And if it is possible you will need a very complicated regex-like formula in
your cell format properties. And I don't know the syntax of formatting cells
based on certain conditions. If you know someone who knows the syntax of
formatting cells by a formula(it looks like regex but isn't) in Excel, he
will be able to tell you if it is possible for sure.

Regards,
Nico.

> Hi,
> To make it more clear; Cell E21 in the sheet "certificates" has the
[quoted text clipped - 26 lines]
>>> less than 50 gives F
>>> What possible way is there for acheiving this task.

Reply to this Message

ChrisM - 19 Apr 2007 14:03 GMT

> Hi,
> To make it more clear; Cell E21 in the sheet "certificates" has the
[quoted text clipped - 25 lines]
>>> less than 50 gives F
>>> What possible way is there for acheiving this task.

Maybe I'm missing something here, but can't you just put something like JE's
formula in the column next to the %mark?
If you don't want the actual mark to be show, just hide that column...

To continue your example,
In Cell F21 put:
=LOOKUP(E21,{49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Signature

Regards,
Chris.
(Remove Elvis's shoes to email me)

Reply to this Message

Khalil Handal - 19 Apr 2007 18:27 GMT

Hi,
The sheet is actually a certificate that will be handed to the student and
its values change depending on which student I select.
I think it will be more easy to have a new sheet with the same design and
contains the grading using A,B,...
I will try to benefit of what JE sujjested.

Thanks

>> Hi,
>> To make it more clear; Cell E21 in the sheet "certificates" has the
[quoted text clipped - 33 lines]
> In Cell F21 put:
> =LOOKUP(E21,{49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Reply to this Message

ChrisM - 20 Apr 2007 15:29 GMT

> Hi,
> The sheet is actually a certificate that will be handed to the
[quoted text clipped - 49 lines]
>> Chris.
>> (Remove Elvis's shoes to email me)

Ok,

Though you could still have the grade mark on the certificate, but just hide
it so that it doesn't actually show when you print the certificate off.
Would just save the (slight) additional complication of adding an extra
sheet.

Signature

Regards,
Chris.
(Remove Elvis's shoes to email me)

Reply to this Message

Khalil Handal - 20 Apr 2007 20:43 GMT

Thanks to all of you. You were of great help

>> Hi,
>> The sheet is actually a certificate that will be handed to the
[quoted text clipped - 57 lines]
> Would just save the (slight) additional complication of adding an extra
> sheet.

Reply to this Message