Grading Marks I

[Adel Handal]

Hi,
This formula is working well:

=LOOKUP((IF(VLOOKUP($A$3,Marks!$B$14:$DO$83,7,FALSE)<>"",(VLOOKUP($A$3,Marks!$B$14:$DO$83,7,FALSE)),"")),{49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

This formula is NOT working well. I am not sure if the problem is with the
part of (IF ISERR) or something else:

=LOOKUP(IF(ISERR(VLOOKUP($A$3,Marks!$B$14:$DO$83,10,FALSE)),"",(VLOOKUP($A$3,Marks!$B$14:$DO$83,10,FALSE))),{49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

This is Based on JE McGimpsey's formula in a previous posting dated 19/4/07
where my formula is added instead of
A1:
=LOOKUP(A1,{49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";
75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Any help is appreciated

 

[T. Valko]

This formula is NOT working well.

What does "not working well" mean?

Any value <49 returned by either formula's VLOOKUP will result in #N/A.

In your first formula:

IF(VLOOKUP($A$3,Marks!$B$14:$DO$83,7,FALSE)<>""

If that is FALSE then the result will be #N/A

In your second formula:

IF(ISERR(VLOOKUP($A$3,Marks!$B$14:$DO$83,10,FALSE))

If that is TRUE then the result will be #N/A

I'm going to take a guess that what you want is this:

First formula:

=LOOKUP(IF(VLOOKUP($A$3,rng,7,0)<>"",VLOOKUP($A$3,rng,7,0),0),{0,"";49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Second formula:

=LOOKUP(IF(ISERR(VLOOKUP($A$3,rng,10,0)),0,VLOOKUP($A$3,rng,10,0)),{0,"";49,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

Biff

 

[Adel Handal]

Any value < 49 should give F .Instead there is the value of #N/A
I didn't notice that both formulas giving the same for ALL values < 49. the
first formula was tested with values greater than 50.

 

[Anthony Gaston]

Adel,

I would start with creating a chart to use as a legend with the grades and
their minimum value to the left as in below (two cells; one number grade and
the other letter grade):

45.00% F
50.00% E+
55.00% E+
60.00% D
65.00% D+
70.00% C
75.00% C+
80.00% B
85.00% B+
90.00% A
95.00% A+

Next name the range something like grades or something (see reference in
formula).

Then use the following formula "=VLOOKUP(B6,grades,2,TRUE)" minus the
outside quotes and you will get the desired results without the long
formula. Good luck.

--
Thanks,

Anthony Gaston
(e-mail address removed)
University of Phoenix
"Raise your role in life above your fear of an un-rewarded purpose." AGaston

 

[RagDyeR]

Since you're attempting to insert an Iserror function, I assume you're
looking to eliminate certain errors *in addition* to correcting the alpha
returns of the test scores.

To correct the alpha returns (<49 = F), simply replace the "49" in your
formula with a zero.

As far as eliminating errors, you need to elaborate on *exactly* which
errors you're looking to trap.

For example, what returns do you want for these circumstances,
That is, considering that they might or might not exist, at all.

*** Student name not found in B14 to B83
*** Test score cell in C14 to DO83 is *empty* (blank cell)
*** Test score cell in C14 to DO83 is zero length string ( "" )

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------



Any value < 49 should give F .Instead there is the value of #N/A
I didn't notice that both formulas giving the same for ALL values < 49. the
first formula was tested with values greater than 50.

 

[Khalil Handal]

Thanks a lot.
I figured out the zero value.
As for the other errors i have empty cells. User sees nothing in the cell on
the certificate.

 

[Khalil Handal]

Thanks!
Long formulas actually confuses me a lot.

 

[RagDyeR]

This will return a blank cell if student name cannot be found,
AND
Will return an "F" for an *empty* score cell, as well as a "zero" ( 0 )
score cell:

=LOOKUP(IF(ISNA(VLOOKUP($A$3,Marks!$B$14:$DO$83,10,0)),"",(VLOOKUP($A$3,Marks!$B$14:$DO$83,10,0))),{"","";0,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})


This will return a blank cell if student name cannot be found,
AND / OR
Score cell is *empty*,
AND
Will return an "F" for a "zero" ( 0 ) score cell,

=LOOKUP(IF(ISNA(VLOOKUP($A$3,Marks!$B$14:$DO$83,10,0)),"",IF(VLOOKUP($A$3,Marks!$B$14:$DO$83,10,0)="","",VLOOKUP($A$3,Marks!$B$14:$DO$83,10,0))),{"","";0,"F";50,"E";55,"E+";60,"D";65,"D+";70,"C";75,"C+";80,"B";85,"B+";90,"A";95,"A+"})

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================




Thanks a lot.
I figured out the zero value.
As for the other errors i have empty cells. User sees nothing in the cell on
the certificate.