Weigh a GPA by credit hours instead of by course count
AVERAGE treats every course the same regardless of size — SUMPRODUCT paired with SUM lets a 4-credit lecture course count four times as much as a 1-credit lab.
Task:
You handle transcript calculations in a university registrar's office. A student's fall semester grades are in front of you, and each course carries a different number of credit hours — a 1-credit lab should not pull the GPA as hard as a 4-credit lecture course. Work out the credit-weighted GPA in C6.
Learning Objectives:
- Use SUMPRODUCT to multiply two parallel ranges and sum the results in one step
- Build a weighted average by dividing a SUMPRODUCT total by the sum of the weights
- Recognize when a plain AVERAGE misrepresents data with unequal weights
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Course | Credit Hours | Grade Points |
| 2 | Calculus I | 4 | 3.7 |
| 3 | Intro to Psychology | 3 | 4 |
| 4 | Chemistry Lab | 1 | 3.3 |
| 5 | World History | 3 | 3 |
| 6 | Weighted GPA |
What this exercise teaches (contains the answer)
SUMPRODUCT(B2:B5,C2:C5) pairs each course's credit hours with its grade points, multiplying and adding in one step, which gives the total quality points the student earned — 14.8 for Calculus, 12 for Psychology, 3.3 for the lab and 9 for History, totaling 39.1. Dividing by SUM(B2:B5), the 11 total credit hours, converts that back into a per-credit figure: 3.5545. A plain AVERAGE of the four grade points would give 3.5, quietly treating the 1-credit lab as if it counted as much as the 4-credit lecture course and understating how much Calculus and Psychology's higher-credit grades should pull the number up.