# Weighted Averages and GPA in Excel: SUMPRODUCT, and Why AVERAGE Is Wrong AVERAGE gives 86.0 where the real grade is 85.0, and 3.20 where the real GPA is 3.29. One SUMPRODUCT fixes both. Here is the build, the letter-grade lookup, and the formula that tells you what you need next semester. --- - **Canonical URL:** https://dothecalculation.com/blog/templates/excel-weighted-average-and-gpa - **Category:** Templates - **Author:** Do The Calculation Team - **Published:** 2026-08-03 - **Reading time:** 10 min read - **Publisher:** Do The Calculation (https://dothecalculation.com) - **Methodology:** https://dothecalculation.com/methodology --- ## Why AVERAGE Gives the Wrong Answer AVERAGE treats every value as equally important. A final exam worth a quarter of the grade and a quiz worth three percent count the same. So does a four-credit course and a one-credit lab. Whenever the items differ in weight, AVERAGE answers a question nobody asked. The correct tool is SUMPRODUCT, which multiplies each value by its weight and adds the results in one pass. This article builds both cases: a weighted course grade and a credit-hour GPA. Tool: [Try the GPA calculator](https://dothecalculation.com/calculators/gpa-calculator) — Enter courses, credits, and grades to get a weighted GPA without building the sheet. ## A weighted course grade **Five categories with different weights** | Category | Score | Weight | | --- | --- | --- | | Homework | 92 | 20% | | Quizzes | 84 | 15% | | Midterm | 78 | 25% | | Project | 95 | 15% | | Final exam | 81 | 25% | **The weighted grade** ``` =SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6) ``` - Result: 85.0 - =AVERAGE(B2:B6) returns 86.0, a full point higher. - Dividing by SUM of the weights makes the formula work whether the weights are percentages, decimals, or raw points. The difference comes entirely from the midterm. It is the lowest score and carries the heaviest weight, so AVERAGE understates its damage. On a grade boundary that single point is the difference between a B and a B plus. > **Always divide by the sum of the weights** — If the weights already sum to exactly 1, the division changes nothing. If a category is dropped, a weight is mistyped, or the weights are expressed as points rather than percentages, the division is what keeps the answer correct. There is no reason to leave it out. ## GPA, which is the same formula with different labels **A semester of 15 credits** | Course | Credits | Grade | Points | | --- | --- | --- | --- | | Calculus II | 4 | B+ | 3.3 | | Organic Chemistry | 4 | B− | 2.7 | | English Literature | 3 | A | 4.0 | | Statistics | 3 | A− | 3.7 | | Chemistry Lab | 1 | C+ | 2.3 | **GPA** ``` =SUMPRODUCT(B2:B6, D2:D6) / SUM(B2:B6) ``` - 49.4 quality points ÷ 15 credits = 3.29 - =AVERAGE(D2:D6) returns 3.20, because it ignores that the two hardest courses carry four credits each. - Quality points are just credits × grade points; most institutions report both. The gap runs the other way here. The one-credit lab has the worst grade and the least weight, so the unweighted average punishes it too much. Weighting can move the answer in either direction, which is why guessing is not a substitute for computing it. ## Turning letters into points automatically Typing the points column by hand is where errors enter. Build a two-column lookup table and let the sheet convert. **Letter to grade points** ``` =XLOOKUP(C2, GradeLetters, GradePoints, NA()) ``` - The fourth argument returns #N/A for an unrecognised letter rather than a silent wrong answer. - Older versions: =VLOOKUP(C2, GradeTable, 2, FALSE), with FALSE for exact match. - Keep the scale on its own sheet so it can be swapped for a different institution. > **Watch the plus and minus characters** — A grade typed as "B–" with an en dash will not match a table entry of "B-" with a hyphen, and XLOOKUP returns #N/A while VLOOKUP without the FALSE returns whatever is nearest. Use data validation on the grade column so only valid letters can be entered. ## Cumulative GPA across semesters Never average the semester GPAs. A 4.0 across 3 credits and a 2.5 across 18 credits is not a 3.25. Sum the quality points and sum the credits, then divide once. **Cumulative GPA** ``` =SUM(QualityPointsColumn) / SUM(CreditsColumn) ``` - Add a QualityPoints column per semester: =Credits * GradePoints. - The cumulative figure then updates automatically as semesters are added. - This is the same weighted-average error as averaging percentages, in a different costume. ## What you need next semester The useful direction is backwards: given where you are and where you want to be, what does the next block of credits have to average? **Required GPA on new credits** ``` =(Target*(CurrentCredits+NewCredits) − CurrentGPA*CurrentCredits) / NewCredits ``` - 58 credits at 3.29, aiming for 3.40 across 15 more credits: (3.40×73 − 3.29×58) ÷ 15 = 3.83 - Aiming for 3.50 instead returns 4.31, which is above the scale and therefore impossible. - A result above 4.0 is the formula telling you the target needs more semesters, not more effort. That last point is the reason to build this rather than guess. The arithmetic of a cumulative average is unforgiving: the more credits already banked, the less any single semester can move it. A student with 100 credits cannot shift their GPA by more than about a tenth in a term, whatever they score. Tool: [Try the cumulative GPA target calculator](https://dothecalculation.com/calculators/gpa-cumulative-target-calculator) — Find the grade average you need across future credits to reach a target cumulative GPA. ## Two variations that come up constantly ### Dropping the lowest score If a category drops its lowest item, exclude it before weighting rather than after. Compute the category score with =(SUM(range) − MIN(range)) / (COUNT(range) − 1), then feed that into SUMPRODUCT. Dropping a score after weighting distorts the weight of everything else. ### Ignoring incomplete categories Mid-semester, some categories have no scores yet. Weighting a blank as zero shows a failing grade in week three. Use =SUMPRODUCT(B2:B6, C2:C6) / SUMPRODUCT((B2:B6<>"")*C2:C6), which sums only the weights of categories that have a score. The result is the grade on work completed so far. ## The four things that go wrong - Using AVERAGE anywhere weights differ. This is the whole subject and it still happens in production gradebooks. - Mismatched range lengths. SUMPRODUCT returns #VALUE! if the two ranges differ in size, which is annoying and much better than silently ignoring rows. - Text in a numeric column. A credit value entered as "3 " with a trailing space is text, and SUMPRODUCT treats it as zero without complaint. Wrap suspect columns in =SUMPRODUCT(--(B2:B6), ...) or clean the data. - Averaging semester GPAs instead of pooling quality points and credits. Correct only in the rare case where every semester has the same credit load. ## What the sheet does not know - How your institution actually computes GPA. Plus and minus handling, whether an A plus is 4.0 or 4.3, and how repeated courses are treated all vary, and none of it is standard. - Which courses count. Transfer credits, pass/fail courses, and withdrawals are often excluded from GPA while still counting toward a degree. - Rounding rules. Whether 3.295 becomes 3.29 or 3.30 is an institutional decision, and it matters at a scholarship threshold. - Grade replacement policies. Many institutions replace rather than average a repeated course grade, which no straightforward SUMPRODUCT captures. - What the number is for. A major GPA, a cumulative GPA, and a GPA in the last 60 credits are three different figures, and graduate admissions may want a specific one. **What is the difference between SUMPRODUCT and AVERAGE?** AVERAGE adds the values and divides by the count, treating everything as equally important. SUMPRODUCT multiplies each value by its weight before adding, so heavier items count more. They agree only when every weight is identical. **Do my weights have to add up to 100%?** Not if you divide by SUM of the weights. That division normalises whatever scale the weights are on, so 20/15/25/15/25 as raw points gives the same answer as the percentages. Always include it. **Why does my SUMPRODUCT return #VALUE!?** Almost always mismatched range sizes, such as B2:B6 against C2:C7. Occasionally it is text in one of the ranges. Check the range endpoints first; it is the cause more than nine times in ten. **How do I handle a course still in progress?** Exclude it from both the quality points and the credits until a grade exists. Use =SUMPRODUCT((D2:D6<>"")*B2:B6*D2:D6) / SUMPRODUCT((D2:D6<>"")*B2:B6) so an ungraded row does not drag the average toward zero. **Can I average two semester GPAs together?** Only if both semesters carried the same number of credits. Otherwise pool the quality points and the credits and divide once. A 4.0 over 3 credits and a 2.5 over 18 credits gives a cumulative 2.71, not 3.25. **What if the required GPA formula returns more than 4.0?** The target is unreachable in that number of credits. Either extend the horizon by adding more credits to the calculation or lower the target. The formula is doing exactly what it should; the answer is simply outside the scale. --- _Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-weighted-average-and-gpa). Quote freely with attribution and a link to this page._