?IF function for preventing a cell being filled by formula

[confused teacher]

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

"confused teacher" <confusedteacher@discussions.microsoft.com> wrote in
message news:4EA32191-C6D6-4DFB-99D7-1A8CA974E4F5@microsoft.com...
>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
>

[confused teacher]

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" wrote:

> 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
>
> "confused teacher" <confusedteacher@discussions.microsoft.com> wrote in
> message news:4EA32191-C6D6-4DFB-99D7-1A8CA974E4F5@microsoft.com...
> >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]

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

"confused teacher" <confusedteacher@discussions.microsoft.com> wrote in
message news:2BB4DA13-3F1B-442E-A06B-9732DB40512B@microsoft.com...
> 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" wrote:
>
>> 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
>>
>> "confused teacher" <confusedteacher@discussions.microsoft.com> wrote in
>> message news:4EA32191-C6D6-4DFB-99D7-1A8CA974E4F5@microsoft.com...
>> >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
>> >
>>
>>
>>