6 Replies Latest reply: Aug 15, 2007 2:10 PM by WWJD
Folklorist Level 1 Level 1 (5 points)
Hiya. Like Anim8me2 (good question about using formulas to assign letter grades to ranges of values), I am building a grade book for teaching. I really want to get away from my clunky and inelegant gradebook program and should be able to if I can solve this one remaining problem:

THE PROBLEM: My sheet has rows of students with their scores on various assignments listed across in columns. (I set the point value of each assignment separately.) I've got Total and % columns to compute each student's points earned/points possible. This works great when every student is responsible for all assignments, but occasionally students are exempt from some items. How, therefore, can I build a formula that will sense when a cell is empty and adjust down the points possible value accordingly?

Here's a sample of my sheet. How do I make Chaucer's total points figure only on those assignments he's completed (as 48/49 instead of 48/69)?


PowerBook G4, Mac OS X (10.4.10)
  • WWJD Level 6 Level 6 (15,825 points)
    You could use the sum of the totals divided by the count of cells with values.

    Regards,

    Photo Sharing and Video Hosting at Photobucket
  • Folklorist Level 1 Level 1 (5 points)
    Thanks, WWJD, I learned something there and tried it out, but I kept getting funky results. Finally figured it out: your solution works to average scores when all assignment values are the same. (I wish I had statistics in college so I'd know the vocab. to use in this situation.)

    Unfortunately, my need is still different in that assignments are assigned different total point values, so I need the calculation to (a) disregard cells that are empty while (b) adding up the total points possible for only those cells that have values in them. I hope I'm explaining it well enough.

    Give me a relational database and I can do it in a few minutes. (But Numbers is so elegant and if I don't have to use a db I'd rather not.)

    CA

    Message was edited by: Folklorist

    Message was edited by: Folklorist
  • WWJD Level 6 Level 6 (15,825 points)
    Oops! I did not read your example fully, now I see. I'll create another example.

    Regards,
  • WWJD Level 6 Level 6 (15,825 points)
    Here's a potential solution (unless the student gets a "0" then you'll have to use another sumif value):

    Photo Sharing and Video Hosting at Photobucket

    Regards,

    Actually ">=0" will handle zero scores.

    Message was edited by: WWJD
  • Folklorist Level 1 Level 1 (5 points)
    WWJD, thanks--you're extremely helpful. I implemented the SUMIF you suggested and it worked. It still didn't handle entries of "0" so I came back to the board to mark your suggestion as helpful, which I did.

    Too late . . . I noticed your edit at the bottom of the message, changing > to >= and that did the trick. So you solved my problem and answered the question, but I'd like to give you credit. Please post one more message here so I can mark it appropriately for you.

    Many, many thanks.

    CA
  • WWJD Level 6 Level 6 (15,825 points)
    Good to hear that the program is working. After posting the initial image, then deciding that >= will handle 0 items.

    Regards,