Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 non-blank 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.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    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 non-blank 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 0-3). 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 drop-quizzes 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •