Excel Grading formula

[dshafique]

Hi guys, I am trying to create a spreadsheet which calculates my students' daily grades and spit out an average at the end of the week. I have certain tasks they do during the day and they're graded for those task for a total of out of 20 points per day. the formula i have right now is just an average of the points for each day.
my problem:

I want to make it in such a way where it only takes into account the days i have filled out so far. for example, if a student does perfectly on monday, i give them 20 points for monday, but my overall grade for the week is only going to give them 20% when it should be 100%.
is there a formula which only can only take into account the days i have graded so my students won't get discouraged?

thanks

 

[pvr928]

Hi dshafique

If your data is in range A1:E1 (representing Monday - Friday) then:

=SUM(A1:E1)/COUNTIF(A1:E1,">0")/20

and then format as "0%"

Cheers

pvr928

 

[dshafique]

Thank you so much, just what I needed. I made a couple edits to it to fit my sheet

=IFERROR(G13/COUNTIF(B13:F13,">0")/20, " ")

i added an if error so it shows it blank instead of #N/A and instead of SUM of A1-e1, i put the sum of all those in cell G1. thank you so much!

 

[Scott Huish]

That's not actually blank, it's a space. To return blank you should use this ""

=IFERROR(G13/COUNTIF(B13:F13,">0")/20, "")