Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Executing a formula based on criteria being met
I have a formula that will generate a grade based on data entered by the
user. my problem being that the grade will still generate even if the operator fails to put all the data in, this could lead to an error in the students grade. I need to ensure that the grade will not calculate and display unless all the results are entered. One of the formulas used to assign a grade is: =IF('DO NOT DELETE'!Z384.99,"HD",IF('DO NOT DELETE'!Z374.99,"D",IF('DO NOT DELETE'!Z364.99,"C",IF('DO NOT DELETE'!Z349.99,"P",IF('DO NOT DELETE'!Z342.99,"PC",IF('DO NOT DELETE'!Z3=0.1%,"F")))))) The result in Z3 will be a SUM of the grades in worksheet "Assessment 2 30% 60% 10% and could be a sum of 6 cells from A1 to F1. My question then is how do I tell Excel to not add the figures unless all 6 cells contain an entry? I assume that I need to put the instruction in front of the formula used in cell Z3 as above. The fomula in cell Z3 is: =SUM('Assessment 2 - 30%, 60% & 10%'!AE8:AH8)*1.666667 If someone has a solution ad can fix the formula for me it would be appreciated. Thanks Brian |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Executing a formula based on criteria being met
Try something like:
=IF(COUNTA(A1:F1)<6,"Data missing",SUM(A1:F1)) You say A1 to F1 but your SUM example is AE to AH which is only 4 entries (confused!) "confused teacher" wrote: I have a formula that will generate a grade based on data entered by the user. my problem being that the grade will still generate even if the operator fails to put all the data in, this could lead to an error in the students grade. I need to ensure that the grade will not calculate and display unless all the results are entered. One of the formulas used to assign a grade is: =IF('DO NOT DELETE'!Z384.99,"HD",IF('DO NOT DELETE'!Z374.99,"D",IF('DO NOT DELETE'!Z364.99,"C",IF('DO NOT DELETE'!Z349.99,"P",IF('DO NOT DELETE'!Z342.99,"PC",IF('DO NOT DELETE'!Z3=0.1%,"F")))))) The result in Z3 will be a SUM of the grades in worksheet "Assessment 2 30% 60% 10% and could be a sum of 6 cells from A1 to F1. My question then is how do I tell Excel to not add the figures unless all 6 cells contain an entry? I assume that I need to put the instruction in front of the formula used in cell Z3 as above. The fomula in cell Z3 is: =SUM('Assessment 2 - 30%, 60% & 10%'!AE8:AH8)*1.666667 If someone has a solution ad can fix the formula for me it would be appreciated. Thanks Brian |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Executing a formula based on criteria being met
The result in Z3 will be a SUM of the grades in worksheet "Assessment 2 30%
60% 10% and could be a sum of 6 cells from A1 to F1. The formula you give for Z3 does not agree w/the above statement with regards to the range reference. Given the formulas you posted, perhaps: =IF(COUNT('Assessment 2 - 30%, 60% & 10%'!AE8:AH8)=COLUMNS('Assessment 2 - 30%, 60% & 10%'!AE8:AH8),SUM('Assessment 2 - 30%, 60% & 10%'!AE8:AH8)*1.666667,"") =IF('DO NOT DELETE'!Z3="","",VLOOKUP('DO NOT DELETE'!Z3,{0,"F";43,"PC";50,"P";65,"C";75,"D";85, "HD"},2)) Change cell references as needed. I assumed anything from 0 through 42.99 should be "F" versus the 0.1% through 42.99. If that's not correct, change the 0 inside of the braces { } or post back. "confused teacher" wrote: I have a formula that will generate a grade based on data entered by the user. my problem being that the grade will still generate even if the operator fails to put all the data in, this could lead to an error in the students grade. I need to ensure that the grade will not calculate and display unless all the results are entered. One of the formulas used to assign a grade is: =IF('DO NOT DELETE'!Z384.99,"HD",IF('DO NOT DELETE'!Z374.99,"D",IF('DO NOT DELETE'!Z364.99,"C",IF('DO NOT DELETE'!Z349.99,"P",IF('DO NOT DELETE'!Z342.99,"PC",IF('DO NOT DELETE'!Z3=0.1%,"F")))))) The result in Z3 will be a SUM of the grades in worksheet "Assessment 2 30% 60% 10% and could be a sum of 6 cells from A1 to F1. My question then is how do I tell Excel to not add the figures unless all 6 cells contain an entry? I assume that I need to put the instruction in front of the formula used in cell Z3 as above. The fomula in cell Z3 is: =SUM('Assessment 2 - 30%, 60% & 10%'!AE8:AH8)*1.666667 If someone has a solution ad can fix the formula for me it would be appreciated. Thanks Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
Create formula that will pull a value based on text in diff cell? | Excel Discussion (Misc queries) | |||
Data Validation - Scroll in the formula bar for a custom criteria | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |