Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
?IF function for preventing a cell being filled by formula
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'!C384.99,"HD",IF('DO NOT DELETE'!C374.99,"D",IF('DO NOT DELETE'!C364.99,"C",IF('DO NOT DELETE'!C349.99,"P",IF('DO NOT DELETE'!C342.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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
?IF function for preventing a cell being filled by formula
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" wrote in message ... 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'!C384.99,"HD",IF('DO NOT DELETE'!C374.99,"D",IF('DO NOT DELETE'!C364.99,"C",IF('DO NOT DELETE'!C349.99,"P",IF('DO NOT DELETE'!C342.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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
?IF function for preventing a cell being filled by formula
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" wrote in message ... 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'!C384.99,"HD",IF('DO NOT DELETE'!C374.99,"D",IF('DO NOT DELETE'!C364.99,"C",IF('DO NOT DELETE'!C349.99,"P",IF('DO NOT DELETE'!C342.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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
?IF function for preventing a cell being filled by formula
He
=IF('Do not Delete'!C3="","",IF('Do not Delete'!C384.99,"HD",IF('Do not Delete'!C374.99,"D",IF('Do not Delete'!C364.99,"C",IF('Do not Delete'!C349.99,"P",IF('Do not Delete'!C342.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" wrote in message ... 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" wrote in message ... 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'!C384.99,"HD",IF('DO NOT DELETE'!C374.99,"D",IF('DO NOT DELETE'!C364.99,"C",IF('DO NOT DELETE'!C349.99,"P",IF('DO NOT DELETE'!C342.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELL Function: cell reference by formula | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
IF Function to test formula in a cell | Excel Worksheet Functions | |||
revealing the contents of a formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |