Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Conditional Summing

    I am teaching a class where students turn in an assignment each week. A big part of their grade is the percentage of these assignments they turn in. The Excel page I used to perform these calculations is shown.

    Cell F1 counts the number of labels in Cells E5 to Z5 to figure out how many assignments have been recorded. I add a new label each week as each assignment is due. The Percentage Complete cells are calculated as =SUM(F6:Z6)/$F$1. The general layout is shown by the attached JPG, with student information grayed out. (There are a lot more students, I'm just showing a few.)

    I don't want to have to change the formulas each week and this arrangement works fine except when a student a student turns in an assignment early. If I go ahead and enter a "1" in that column, their percentage is wrong. If I go ahead and create a label, everyone else's percentage is wrong. Is there a way to handle this without having to rewrite the formula each week?


    Ronny
    Attached Images Attached Images
    Ronny Richardson

  2. #2
    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
    Use:
    =SUMIF($F$5:$Z$5,"<>",F6:Z6)/$F$1
    and don't add a new label until appropriate.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    Ronny (2011-09-28)

  4. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post
    Very cool and extremely easy. Thanks.

    Ronny
    Ronny Richardson

Posting Permissions

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