Grading Spreadsheet

[nmhernandez13]

I'm a TA for Introduction to Entomology and I keep an excel spreadsheet with all my grade calculations and send my students a blank version so they can keep track of their own scores if they want. Right now I have the basic formulas that add up each cell and divide by the total number of points to get the final percentages. I'm curious if there is a formula I can use to give the students an idea of how they're doing along the way, so it would ignore blank cells (for example, an exam they haven't taken yet) and give them a percentage based on what has been completed so far. I've tried looking around and finding this information on my own but so far no luck.

This is the point breakdown if that will help:
Lab Component = 500 Points = 50% of total grade
Drawing 1 - 10 points
Drawing 2 - 5 points
Drawing 3 - 10 points
Drawing 4 - 5 Points
Label Assignment - 25 Points
Lab Exam 1 - 65 Points
Lab Exam 2 - 65 Points
Lab Exam 3 - 65 Points
Lab Exam 4 - 75 Points
Insect Collection - 175 Points
Extra credit - varies by semester, usually 20 points that they can add on to their lab grade, no penalty for not doing it
Lecture Component = 300 Points = 50% of total grade
Lecture Exam 1 = 100 Points
Lecture Exam 2 = 100 Points
Lecture Exam 3 = 100 Points

[Tbalot]

Before I start, How are 500 points and 300 points each 50%?

*edit*
I included a sample gradebook

GradeBook.xlsx

[nmhernandez13]

So if a student gets 450/500 points or a 90% on the lab portion, that's 45% towards their final grade. If they get 225/300 or a 75% on the lecture portion, that is 37.5% towards the final grade. So their final grade is 45+37.5 = 82.5% overall.

*edit*
Didn't see your edit, sorry! I'll check that out.

[nmhernandez13]

Yeah that's close, but it doesn't account for the fact that the lecture portion is worth 50% and the lab portion is worth 50%, so if I add in points it just does it out of 800 total, and that isn't how our grading works. Right now when I put in numbers, if I make the lab grade an 80% and the lecture grade a 90% then it reports the overall grade as an 81.25% and it should be 85%.

[Tbalot]

GradeBook.xlsx

Try Again

[nmhernandez13]

Yes!!! That is perfect, thank you so much. I've been trying to figure this out on my own for a while but obviously this is not my area of expertise! Really appreciate it.

[Tbalot]

No problem