How to Calculate Weighted Grades in Excel and Google Sheets
The setup and the formula
Lay the data out as three columns — category, grade, weight:
| A | B | C | |
|---|---|---|---|
| 1 | Category | Grade | Weight |
| 2 | Homework | 95 | 20 |
| 3 | Quizzes | 84 | 30 |
| 4 | Exams | 88 | 50 |
| 5 | Weighted grade | =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) | |
The result is 88.2. SUMPRODUCT computes 95×20 + 84×30 + 88×50 = 8,820 in one step, and dividing by SUM(C2:C4) = 100 finishes the weighted average. Because you divide by the actual sum of weights, this formula stays correct mid-semester too — just leave untaken categories out of the ranges (or leave both cells blank; a blank counts as 0 in both parts, which cancels correctly only if grade AND weight are blank together).
Why you're getting #VALUE! (and other wrong answers)
Text that looks like numbers. Typing 95% as text, or a grade with a stray space ("88 "), makes SUMPRODUCT choke or silently miscount. Fix: enter plain numbers, and use Format → Number if percent signs are involved.
Mismatched ranges. =SUMPRODUCT(B2:B4,C2:C5) — three grades against four weights — returns #VALUE! in Excel. Both ranges must be the same size.
Words in the range. If your range accidentally includes the header row (B1:B4), the text "Grade" breaks the multiplication.
Percent-formatted weights. If C2 shows 20% it's really 0.20 — that's fine, since dividing by SUM keeps it consistent. The error is mixing: 0.20 in one row and 30 in another skews everything. Pick one convention.
When the spreadsheet is overkill
A spreadsheet earns its keep when you're tracking a whole semester across five courses, projecting scenarios, or dropping lowest scores with extra formulas. For the one-off question — "what's my grade in this class right now?" — building, formatting and debugging a sheet takes longer than typing three rows into a purpose-built calculator that already handles empty categories and mid-semester weighting. Use the tool that matches the job's size.
Try it yourself: Weighted Grade Calculator
Don't want to build a spreadsheet for one course? The calculator does the same math in ten seconds.
Frequently asked questions
Does Google Sheets have a built-in weighted average function?
Not a general-purpose one — SUMPRODUCT divided by SUM is the standard approach and matches Excel exactly. (Sheets' AVERAGE.WEIGHTED exists but Excel has no equivalent, so SUMPRODUCT is the portable habit.)
Can I make the formula ignore categories I haven't started?
Yes — leave both the grade and weight cells empty and SUMPRODUCT/SUM skips them naturally. If you must keep a weight visible, use =SUMPRODUCT(B2:B4,C2:C4)/SUMPRODUCT((B2:B4<>"")*C2:C4) to divide only by weights whose grade cell is filled.
Why does my spreadsheet disagree with my school's portal?
Usually a dropped-lowest-score policy, an assignment marked excused, or a category that the portal excludes until it has grades. Your formula is doing what you told it; the portal is applying syllabus rules you haven't replicated.