How to Differentiate between a Student's Score of "0" and a Score of Null?

[Arnold]

Hi Excel-lent People,
I am trying to average assignment scores in a gradebook. Assignment
scores are entered into cols starting with AO. On row 10 are the max
points possible for assignments (for instance, AO10 may be 20 for 20
points poss). Students begin on row 13, and, if students do not need
to do an assignment, I will leave their cell blank for that
assignment. I cannot solve how to write a formula that will exclude
null cells when max values are still present in row 10. Here's what I
have:

Formula in O13 to compute Assign. Points Possible:
=SUMIF(AO13:AO13,">0",AO$10:AO$10)

Formula in P13 to compute Assign. Points Earned:
=SUMIF(AO13:AO13,">0",AO$13:AO$13)

Formula in Q13 to compute Assign. % Earned:
=AVERAGE(IF(($AO13:AO13<>"")*($AO$10:AO$10<>0),$AO13:AO13/$AO$10:AO
$10))*100

**Here's the key--the student in row 13 may get a 0 on an assignment--
AO13 would = 0 and then Q13 would then = 0.00, which is the same
result as if the student didn't have to do the assignment. Other
formulas will use this value of "0"

If AO13 is left blank, how can this formula produce the desired
result?

I would like to simply figure each assignment percent for each
student, then average all of the assignments for a student during a
quarter.

Thanks a bunch!!!

 

[Corey]

Can you not set the sheet to Not display Zero values.
Then differentiate the formulas with a [("")No value] entered and a [(0)Zero] value ?


Corey....
Hi Excel-lent People,
I am trying to average assignment scores in a gradebook. Assignment
scores are entered into cols starting with AO. On row 10 are the max
points possible for assignments (for instance, AO10 may be 20 for 20
points poss). Students begin on row 13, and, if students do not need
to do an assignment, I will leave their cell blank for that
assignment. I cannot solve how to write a formula that will exclude
null cells when max values are still present in row 10. Here's what I
have:

Formula in O13 to compute Assign. Points Possible:
=SUMIF(AO13:AO13,">0",AO$10:AO$10)

Formula in P13 to compute Assign. Points Earned:
=SUMIF(AO13:AO13,">0",AO$13:AO$13)

Formula in Q13 to compute Assign. % Earned:
=AVERAGE(IF(($AO13:AO13<>"")*($AO$10:AO$10<>0),$AO13:AO13/$AO$10:AO
$10))*100

**Here's the key--the student in row 13 may get a 0 on an assignment--
AO13 would = 0 and then Q13 would then = 0.00, which is the same
result as if the student didn't have to do the assignment. Other
formulas will use this value of "0"

If AO13 is left blank, how can this formula produce the desired
result?

I would like to simply figure each assignment percent for each
student, then average all of the assignments for a student during a
quarter.

Thanks a bunch!!!

 

[Arnold]

Thanks for responding Corey,
Setting the options for the sheet to not display zero values won't
work because some students might actually get a 0, which needs to be
recorded. The above formulas would compute 0 points, and thus, 0
percent. Some students may have an excused absence and not need to do
an assignment. For these, their score cell should remain blank.
However, in the formula columns, those still compute as 0%--lowering
the overall grade. The formula above should exclude these null
instances. This is turning out to be more difficult than I imagined...

 

[Mike]

Thanks for responding Corey,
Setting the options for the sheet to not display zero values won't
work because some students might actually get a 0, which needs to be
recorded. The above formulas would compute 0 points, and thus, 0
percent. Some students may have an excused absence and not need to do
an assignment. For these, their score cell should remain blank.
However, in the formula columns, those still compute as 0%--lowering
the overall grade. The formula above should exclude these null
instances. This is turning out to be more difficult than I imagined...

If I understand the question correctly. the grades will be listing in
columns AO, AP, AQ, AR... with total possible in row 10 and the
student in row 13

try using the following formulas
for column O (total possible points)
=SUMIF(AO13:AR13,">-1",AO$10:AR$10)
for column P (total points for student in row 13)
=SUMIF(AO13:AR13,">-1",AO$13:AR$13)
for column Q
=AI13/AH13*100
or
=SUMIF(AO13:AR13,">-1",AO$13:AR$13)/SUMIF(AO13:AR13,">-1",AO$10:AR
$10)*100

these formulas will also allow text to be entered and not added in to
the average.

a score of 0 will meet the criteria of >-1 but a blank or null value
did not when I tested it.

 

[Arnold]

Hi Mike,
Your re-wording of my problem is correct. Grades are listed in cols.
AO, AP, AQ, AR... with total possible in row 10 of each col. and
students down col. A.

Your formula works if there is there is a numeric value in AO13 or
AP13. However, it shows #DIV/0! if AO13 is left null.

I tried to redo some of the absolute / relative references to account
for the fact that I only have col. AO right now (through time, AP, AQ,
AR, etc. will be added; we don't know the end of the range though).
However, I got the same #DIV/0! result.

=SUMIF($AO13:AO13,">-.1",$AO13:AO13)/SUMIF($AO13:AO13,">-.1",$AO$10:AO
$10)*100

I think we're close. Any other suggestions? I, and other teachers,
appreciate your help.
Arnold

 

[Mike]

Hi Mike,
Your re-wording of my problem is correct. Grades are listed in cols.
AO, AP, AQ, AR... with total possible in row 10 of each col. and
students down col. A.

Your formula works if there is there is a numeric value in AO13 or
AP13. However, it shows #DIV/0! if AO13 is left null.

I tried to redo some of the absolute / relative references to account
for the fact that I only have col. AO right now (through time, AP, AQ,
AR, etc. will be added; we don't know the end of the range though).
However, I got the same #DIV/0! result.

=SUMIF($AO13:AO13,">-.1",$AO13:AO13)/SUMIF($AO13:AO13,">-.1",$AO$10:AO
$10)*100

I think we're close. Any other suggestions? I, and other teachers,
appreciate your help.
Arnold

you could put a simple if then to check if a the denominator of the
equation will be zero, if so return "N/A", otherwise return the score
=IF(SUMIF($AO$13:$AR$13,">-1",$AO$10:$AR$10)=0,"N/A",SUMIF($AO
$13:$AR$13,">-.1",$AO$13:$AR$13)/SUMIF($AO$13:$AR$13,">-1",$AO$10:$AR
$10))

the first part of the equation test the sum if of the total posible
score based on if there is a student score or not. If the total
score, student score or both are blank the sumif will result in 0 and
the logic test will return true. if the total points and the student
have a score >-1 the equation will return the sum of the student
scores / total posible. you can substitue any column for AR. the
formula will only use the data if a total posible is entered.

 

[Arnold]

Thanks for the help!