Results 1 to 4 of 4
Thread: Distinguish zeros (2003)

20070114, 22:53 #1
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,934
 Thanks
 6
 Thanked 9 Times in 7 Posts
Distinguish zeros (2003)
Hi All,
I need to find a way to distinguish a grade of 0 for one reason vs another when using formulas.
I maintain a grading sheet in Excel for my algebra class. Two of the items I keep track of for statistical purposes in a "DB" sheet are quizzes taken and quizzes given for each student. This is based on the student's quiz grades which appear in cols B:J (each row for a different student) in the 'quiz grades' worksheet. Before a quiz is given, the cols are blank. When I give a quiz, I fill in the students' grades going down the col for that quiz.
For the DB sheet, the count of quizzes given is pretty easy. I use the formula COUNTIF('quiz grades'!B11:J11,">=0") for the student in row 11, similarly for row 12, etc. Since you can't get a mark lower than 0, this is ok even if the student is absent (absences count against them as a 0 but I can always give a makeup). So counting the number of grades >=0 works fine.
Quizzes taken is more difficult. Ordinarily, if the student is absent, his/her grade of 0 would not be counted as a quiz taken. The assumption was that you'd earn at least some points on a quiz (I'm a liberal marker).
However, we have something called a Homework Quiz. This is where the quiz sheet will identify 4 or 5 problems as something like "Page 12, #4". Students are allowed to take out their notebook where, presumably, their HW was done, but NOT their textbook. They copy the work from notebook to quiz sheet. HW is not really checked other than with this mechanism (but we do review HW if students have questions).
In my first semester teaching 2 classes, I had NO students who didn't do at least some HW so a 0 on a HW quiz never occurred. Next semester, I ran across some. So they hand in a blank sheet and get a 0.
I want to count this case of not doing HW (grade=0) as a quiz taken (as opposed to being absent, regardless of the quiz type, with a grade of 0 as a quiz not taken).
The formula I'm using right now for quizzes taken is COUNTIF('quiz grades'!B11:J11,">0"). What I did the 2nd semester is do a manual adjustment to add 1 to the quizzes taken on the DB sheet.
As far as the student's quiz average, a 0 is a 0 so it doesn't matter the reason.
Any ideas of how to distinguish the 2 cases? There might be other cells I could look at to know when a HW quiz is given and use something like a SUMPRODUCT formula but I'd prefer not to have to take this step.
TIA.
Fred

20070114, 23:05 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Distinguish zeros (2003)
You could use =COUNT('quiz grades'!B11:J11) or =COUNTA('quiz grades'!B11:J11).
COUNT counts the cells with number values, and COUNTA counts the number of nonblank cells.
Note: the AVERAGE function distinguishes between 0 and blank: the average of { 7, 0, 5 } is 12 / 3 = 4, but the average of { 7, blank, 5} is 12 / 2 = 6.

20070114, 23:14 #3
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,300
 Thanks
 3
 Thanked 204 Times in 188 Posts
Re: Distinguish zeros (2003)
Fred,
Can't you just leave the absences blank? It would make more sense to me that way anyhow. <code>COUNTIF(range,">=0")</code> ignores blank cells, at least in XL2002.Regards,
Rory
Microsoft MVP  Excel

20070115, 02:31 #4
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,934
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: Distinguish zeros (2003)
Hi Hans, Rory,
Thanks much. I think you may be suggesting leaving something blank. I'm not sure if I can leave the quiz score blank because that's how the cells are initialized; it's the presence of a nonblank that tells me the quiz was given.
I think the problem with leaving an absence blank is also that I'm not sure if that would count as a 0. On the 'quiz grades' sheet, I record the quiz scores and calculate the quiz average. However, back when I started teaching (around Sept 2005), Hans provided me with a formula that would calculate the quiz average but allow dropping the x lowest quiz scores (where x is a number like 03). I never really understood how that formula worked but I satisfied myself that it did work.
It may be time for me to look to see if that formula would still work if I left scores blank for absence. It does make more sense as Rory says but I have to make sure the quiz average calculation still works (ie, a blank would have to be counted as 0).
The formula was
{=IF(COUNT(B11:J11)>0, SUM(LARGE(B11:J11,ROW(INDIRECT("1:"&MIN(COUNT(B11: J11),MAX(drop_quizzes,COUNT(B11:J11)drop_quizzes)))))) / MIN(COUNT(B11:J11),MAX(drop_quizzes,COUNT(B11:J11)drop_quizzes)), "no quizzes")}
where dropquizzes is a cell name for the number of the lowest quiz scores to drop and the text string "no quizzes" is how I initialize the average if the IF test fails.
Need to do some more thinking.
Fred