•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
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]
Want to read more?
Check out the full article on the original site
Tagged with
#Excel alternatives for data analysis
#no-code spreadsheet solutions
#Excel compatibility
#Excel alternatives
#natural language processing for spreadsheets
#generative AI for data analysis
#row zero
#rows.com
#AI formula generation techniques
#cloud-based spreadsheet applications
#formula generator
#big data performance
#financial modeling with spreadsheets
#Excel
#gradebook
#equal-weight average
#overall grade
#student test scores
#percentage
#total marks