Executing a formula based on criteria being met

[Guest]

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'!Z3>84.99,"HD",IF('DO NOT DELETE'!Z3>74.99,"D",IF('DO NOT
DELETE'!Z3>64.99,"C",IF('DO NOT DELETE'!Z3>49.99,"P",IF('DO NOT
DELETE'!Z3>42.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

 

[Guest]

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!)

 

[Guest]

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.