How to Calculate a Final Grade from Weighted Categories in Excel

Average each grading category, combine them with a weights table into a final grade, and apply a drop-the-lowest-score policy.

You will learn to

• Average one category from a list of assignments
• Combine category averages with a weights table
• Drop the lowest score from a category average

How it works

Most syllabi weight categories, not individual assignments: homework 20%, quizzes 15%, labs 25%, exams 40%. First average each category — AVERAGEIF works straight from a list of assignments — then multiply each category average by its weight and add them up. With a weights table, one formula does it all: =SUMPRODUCT(F2:F5,AVERAGEIF(A2:A11,E2:E5,C2:C11)).

Categories with many assignments don’t get extra influence; only the weights matter. Many teachers also drop each student’s lowest homework score — subtract the lowest with MINIFS and divide by one fewer assignment.

=SUMPRODUCT(F2:F5,AVERAGEIF(A2:A11,E2:E5,C2:C11))