6 Replies Latest reply: Aug 15, 2007 2:10 PM by WWJD
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)
• Level 6 (15,825 points)
You could use the sum of the totals divided by the count of cells with values.

Regards,

• 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
• Level 6 (15,825 points)
Oops! I did not read your example fully, now I see. I'll create another example.

Regards,
• 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):

Regards,

Actually ">=0" will handle zero scores.

Message was edited by: WWJD
• 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
• 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,