?IF function for preventing a cell being filled by formula

[Guest]

I am trying to figure out a function (I assume it will be an IF funtion) that
will prevent a cell being filled by an "F" based on the formula that affects
that cell. It is for a gradebook and the cell is automatically filled by an F
based on the function i use.

There are two or three sections to each criteria and this needs to be
assigned an overall grade and then a final grade. As an example in the
worksheet the cells T5:V5 contain the following formula
=IF(ISNA(VLOOKUP(D5,{"HD+",4.8;"HD",4.7;"HD-",4.3;"D+",4.15;"D",3.95;"D-",3.75;"C+",3.6;"C",3.45;"C-",3.3;"P+",3.15;"P",2.85;"P-",2.5;"F+",2.15;"F",1.5;"F",0},
2,FALSE)),"",VLOOKUP(D5,{"HD+",4.8;"HD",4.7;"HD-",4.3;"D+",4.15;"D",3.95;"D-",3.75;"C+",3.6;"C",3.45;"C-",3.3;"P+",3.15;"P",2.85;"P-",2.5;"F+",2.15;"F",1.5;"F",0},
2,FALSE)) which assigns a numeric grade for the letter that the user enters
in the cell D5:F5.

The criteria grade for the criterion in question is located in cell W5 and
is determined as a letter based on the following formula: =IF('DO NOT
DELETE'!C3>84.99,"HD",IF('DO NOT DELETE'!C3>74.99,"D",IF('DO NOT
DELETE'!C3>64.99,"C",IF('DO NOT DELETE'!C3>49.99,"P",IF('DO NOT
DELETE'!C3>42.99,"PC",IF('DO NOT DELETE'!C3>=0%,"F"))))))

The problem is when i copy the formula down to accomodate the number of
students, Excell automatically inserts a "F" grade in the cells, not only
does it look untidy, it increases the risk of a fail being assigned in error
to a student. Is there a formula that will stop Ecel doing this?

Thanks

Brian

 

[Biff]

Your second formula is the only one of the two that can return a "F".

.....IF('DO NOT DELETE'!C3>=0%,"F"))))))

If C3 is empty it will equal 0 and return the "F". Try putting this at the
very beginning of that formula:

=IF('DO NOT DELETE'!C3="","",the_rest_of_the_formula)

Or:

=IF('DO NOT DELETE'!C3="","",LOOKUP('DO NOT
DELETE'!C3,{0;43;50;65;75;85},{"F";"PC";"P";"C";"D";"HD"}))

Also, in you first formula:

...........;"F",1.5;"F",0},2,FALSE))

The 0 will never be returned since you have multiple instances of "F". The
lookup ALWAYS returns the first instance.

Biff

 

[Guest]

Thanks Biff, I entered the formulas, however Excell thinks there is an error.

I typed it in immediately after the "=" at the strat of the formula, how do
I join the two formulas together so that the new bit at the front meshes with
the remainder of the formula that I had originally?

Thanks

Brian

 

[Biff]

Here:

=IF('Do not Delete'!C3="","",IF('Do not Delete'!C3>84.99,"HD",IF('Do not
Delete'!C3>74.99,"D",IF('Do not Delete'!C3>64.99,"C",IF('Do not
Delete'!C3>49.99,"P",IF('Do not Delete'!C3>42.99,"PC",IF('Do not
Delete'!C3>=0%,"F")))))))

Why not use this much shorter formula that does EXACTLY the same thing:

=IF('Do not Delete'!C3="","",LOOKUP('Do not
Delete'!C3,{0;43;50;65;75;85},{"F";"PC";"P";"C";"D";"HD"}))

Biff