Grading - Marks

[Adel Handal]

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.

 

[JE McGimpsey]

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+"})

 

[Adel Handal]

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

 

[Adel Handal]

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.

 

[Nico]

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.

 

[ChrisM]

In message %[email protected],

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.

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+"})

 

[JE McGimpsey]

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

 

[Khalil Handal]

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

 

[ChrisM]

In message (e-mail address removed),

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

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.

 

[Khalil Handal]

Thanks to all of you. You were of great help

In message (e-mail address removed),


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.