Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2006
    Location
    Michigan, USA
    Posts
    16
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Exclude cells in calculation (Excel 2003)

    I have a spreadsheet that I am using that I would like to do a calculation that excludes cells in a column, if the last entry in a column is blank.
    I have students turning in assignments early, and I don't want their grade percentage inflated. I do not enter the points for the assignment (in the bottom row) until the assignment is due. I'd like the calculation to exclude the numbers, in the column, above the TOTAL POINTS cell.

    I have attached a copy of the spreadsheet.
    I am entering the students points for the assignments in columns H through AG. Column AH totals the points. I would like the total in AH to 'look' at the total points row (row 8) and if a cell is blank to exclude the the numbers above it in the total in AH.

    The example in the attachment: Gradebook.xls
    Column W has an entry in row 5 (he handed in the assignment early), but I have not entered in the points possible in column W - row 8. Is there a way to exclude the entry in column W - row 5 from the total in column AH - row 5?

    Hope this makes sense!

    Thanks,

    Bart Putnam

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Exclude cells in calculation (Excel 2003)

    Check to see if this will work: =SUM(IF(H8:AG8="",0,H5:AG5))

    Note: enter it as an array formula by pressing CTRL + SHIFT+ENTER

    PS: Welcome to the lounge...
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exclude cells in calculation (Excel 2003)

    Welcome to Woody's Lounge!

    You can use the SUMIF function for this:
    <code>
    =SUMIF($H$8:$AG$8,"<>",H5:AG5)
    </code>
    The first argument in SUMIF is the range on which you want to test. I have made the reference absolute, so that it will not change if you fill the formula down to AH6 and AH7.
    The second argument is the condition, in the form of a quoted string. <> means not blank.
    The third argument is the range to sum. This reference is relative, so that it will be adjusted automatically if you fill down.

  4. #4
    New Lounger
    Join Date
    Oct 2006
    Location
    Michigan, USA
    Posts
    16
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Exclude cells in calculation (Excel 2003)

    Thanks !!!!

    I will try them both when I get a chance (actually supposed to be working).

    Bart

  5. #5
    New Lounger
    Join Date
    Oct 2006
    Location
    Michigan, USA
    Posts
    16
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Exclude cells in calculation (Excel 2003)

    Hans,
    Worked beautifully - Thanks!

    Rudi,
    Not sure how to enter the formula "by pressing CTRL + SHIFT+ENTER"
    Not familiar with array formulas (but would like to learn).
    Thanks!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exclude cells in calculation (Excel 2003)

    Simply type Rudi's formula in the formula bar (or copy and paste it), and then, instead of pressing Enter or clicking the green checkmark button, press Ctrl+Shift+Enter, i.e. hold down Ctrl and Shift, then press Enter, then release Ctrl and Shift.

    Type array formula in the Help task pane for more info.

  7. #7
    New Lounger
    Join Date
    Oct 2006
    Location
    Michigan, USA
    Posts
    16
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Exclude cells in calculation (Excel 2003)

    Thanks Hans,
    I got the array formula to work.

    Thanks again!!

Posting Permissions

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