Calculating Grade Percentage Excluding Blank Cells

[annrbennett]

Please help....
I need a formula that will calculate the total percentage, excluding blank cells (assignments not yet graded).
Currently, I get 52.50% by dividing the sum of column B by the sum of column C, but that's not correct because I need the blank cells to be excluded.
Any suggestions???

<tbody>
[TD="class: xl67"] A
[/TD]
[TD="class: xl67, width: 99"] B [/TD]
[TD="class: xl67, width: 99"] C [/TD]

[TD="class: xl66"]Assignment[/TD]
[TD="class: xl68, width: 99"]Points
Received[/TD]
[TD="class: xl68, width: 99"]Points
Possible[/TD]

[TD="class: xl65"]HW 1[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]5[/TD]

[TD="class: xl65"]HW 2[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]5[/TD]

[TD="class: xl65"]HW 3[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]5[/TD]

[TD="class: xl65"]HW 4[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]5[/TD]

[TD="class: xl65"]HW5[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]5[/TD]

[TD="class: xl65"]HW6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]5[/TD]

[TD="class: xl65"]HW7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]5[/TD]

[TD="class: xl65"]HW8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]5[/TD]

</tbody>

 

[rossmack2129]

hi annrbennett,

To do what you need to do you could either use a subtotal with a filter or alternatively use a sumif to only sum column C values when column B isn't blank.

In this case you could use:

=SUM(B$2:B9)/SUMIF(B$2:B9,"<>"&"",C$2:C9)


In the above we assume your data is running from B2:B9 and C2:C9 - just change these to fit your data requirements.

Hope that helps,

Cheers,
Ross