•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Calculate equal-weight test averages with different totals and missing scores in Excel
Our take
Calculating equal-weight test averages in Excel can be challenging, especially when dealing with varying total marks and missing scores. As a teacher, your goal is to ensure each test contributes equally to your students' overall grades, regardless of its maximum score. To achieve this, you need a formula that accurately converts scores to percentages, averages them equally, and ignores any blanks. This solution will streamline your gradebook and automatically update overall percentages for each student, making your grading process more efficient and effective.
Hello, I’m a teacher trying to build a gradebook in Excel to track student test scores, but I’m struggling to calculate the overall grade correctly.
I will have around 12 tests throughout the year, and each test has a different total number of marks (e.g., some out of 10, some out of 20, etc.).
What I need:
- Each test should contribute equally to the student’s final grade, regardless of its total marks (e.g., 8/10 and 16/20 should be treated as equal performance)
- Missing tests (blank cells) should be ignored and not counted as 0
- The final result should be a percentage that updates automatically
Current setup:
- Columns F onward = individual tests (Lesson 1, Lesson 2, etc.)
- Row 3 = total marks for each test
- Student scores are entered in each row
- Column E is intended to show each student’s overall percentage
Issues encountered:
- I've tried using
=SUM(scores)/SUM(totals)but it creates a weighted average based on total points, which I do not want - Whe I used formulas like
AVERAGE(F6/F$3, ...)orAVERAGEIFit causes errors:#DIV/0!when totals or scores are blank#VALUE!when combining IF or array formulas
- Excel (Korean version) does not seem to support range division like
F6:O6 / F$3:O$3Or if it does, I can't wrap my head around how to make them work.
Goal:
- A formula that converts each score to a percentage, averages them equally, ignores blanks, and can be filled down for all students
- I would prefer not to add extra rows per student, but a helper column is fine
Optional:
- A way to calculate the class average (row 5) from these results
Screenshots of my current setup are included for reference. My excel is stuck in Korean though and I can't change it due to my administration permissions. I don't speak Korean either!
Thank you in advance for any help.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience