To get a weighted grade in a spreadsheet, multiply each category average by its weight, add the results and divide by the total weight: =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). Google Sheets also has =AVERAGE.WEIGHTED(B2:B4,C2:C4), which gives the same answer.
The steps below build one gradebook in either app, then fix the spots where sheets often go wrong.
The example gradebook
Made-up example. One student, three weighted categories. Every score is a percent, so each one counts the same inside its category.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Category | Average | Weight | Score 1 | Score 2 | Score 3 | Score 4 |
| 2 | Homework | 20 | 75 | 100 | 90 | 95 | |
| 3 | Quizzes | 30 | 85 | 75 | 80 | ||
| 4 | Tests | 50 | 88 | 92 | |||
| 6 | Grade |
Steps 1 and 2 fill in column B.
Step 1: Average each category with AVERAGE
In B2, type =AVERAGE(D2:G2) and fill it down to B4.
- Homework: (75 + 100 + 90 + 95) ÷ 4 = 360 ÷ 4 = 90.00
- Quizzes: (85 + 75 + 80) ÷ 3 = 240 ÷ 3 = 80.00
- Tests: (88 + 92) ÷ 2 = 180 ÷ 2 = 90.00
G3 is empty, so AVERAGE divides the quizzes by 3. Step 5 explains why.
Step 2: Weight the categories with SUMPRODUCT
In B6, type =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4).
SUMPRODUCT multiplies each average by the weight in the same row, then adds the products. Dividing by the sum of the weights keeps the math right even if they do not add up to 100.
(90 × 20 + 80 × 30 + 90 × 50) ÷ (20 + 30 + 50) = (1,800 + 2,400 + 4,500) ÷ 100 = 8,700 ÷ 100 = 87.00
Weights typed as 20%, 30% and 50% give the same 87.00. Both ranges must be the same size. If they are not, Microsoft says SUMPRODUCT returns a #VALUE! error.
Step 3: Use AVERAGE.WEIGHTED in Google Sheets
In Sheets, =AVERAGE.WEIGHTED(B2:B4,C2:C4) takes the values first and the weights second. It returns 87.00 for this sheet.
Google lists three rules for the weights: none can be negative, zero is allowed, and at least one must be positive. The SUMPRODUCT version works in both apps, so keep it if the file might open in Excel.
Step 4: Points-based classes use SUM over SUM
If your class adds up points instead of weighting categories, divide the points earned by the points possible.
Made-up example.
| Row | A | B | C |
|---|---|---|---|
| 1 | Item | Earned | Possible |
| 2 | Essay | 45 | 50 |
| 3 | Quiz | 18 | 20 |
| 4 | Test | 85 | 100 |
| 5 | Lab | 26 | 30 |
=SUM(B2:B5)/SUM(C2:C5)*100 gives (45 + 18 + 85 + 26) ÷ (50 + 20 + 100 + 30) × 100 = 174 ÷ 200 × 100 = 87.00.
Watch the Possible column. Say the test is not graded yet, so B4 is blank, but C4 still says 100. The bottom sum still counts those 100 points, so the blank works like a zero: 89 ÷ 200 = 44.50%. Leave C4 blank too until the test is graded, and the grade is 89 ÷ 100 = 89.00%. The Grade Calculator treats a blank score the second way.
For extra credit, put the points in Earned and leave Possible blank or 0. Five bonus points make 179 ÷ 200 = 89.50%. The Grade Calculator does the same when you check Extra credit on a row or give it a max of 0.
Step 5: Blanks, zeros and text
Microsoft’s AVERAGE page says empty cells and text in a range are ignored, but cells with zero are included. Google’s AVERAGE page also says text is ignored. Here is what G3 does to this student’s grade:
| G3 holds | Quizzes average | Weighted grade |
|---|---|---|
| Nothing | 240 ÷ 3 = 80.00 | 87.00 |
| 0 | 240 ÷ 4 = 60.00 | 81.00 |
| “EX” or “missing” | 240 ÷ 3 = 80.00 | 87.00 |
With the zero: (90 × 20 + 60 × 30 + 90 × 50) ÷ 100 = 8,100 ÷ 100 = 81.00.
The catch is text. AVERAGE skips “missing” exactly as it skips “EX”. If missing work should count as zero, type 0.
SUMPRODUCT works the other way. Microsoft says it treats non-numeric entries as zeros. So a blank or text average in column B adds nothing to the top of the fraction, while its weight still counts at the bottom. Google’s SUMPRODUCT page does not say how it handles text, so test one row in Sheets.
Step 6: A category with no scores yet
Made-up example. The same student at midterm. Homework and Quizzes are as above, but the Tests row is empty.
With no numbers in D4:G4, AVERAGE has nothing to average, and B4 shows an error. Wrap the average so an empty row stays blank. Put this in B2 and fill it down:
=IF(COUNT(D2:G2)=0,"",AVERAGE(D2:G2))
COUNT counts only numbers, so it is 0 for an empty row. Now the Step 2 formula shows:
(90 × 20 + 80 × 30) ÷ 100 = 4,200 ÷ 100 = 42.00
That still divides by all 100 points of weight, so it treats the missing tests as zeros. To leave the empty category out, divide only by the weight of categories that have scores. In H2, fill down =IF(COUNT(D2:G2)=0,0,C2), which gives 20, 30 and 0. Then in B6:
=SUMPRODUCT(B2:B4,H2:H4)/SUM(H2:H4)
(90 × 20 + 80 × 30) ÷ (20 + 30) = 4,200 ÷ 50 = 84.00
That 84.00 is the grade so far, not the final grade. The Weighted Grade Calculator works the same way: a category with nothing graded stays out of the divisor. With these numbers it shows 84%, plus Best possible 92% and Lowest possible 42%.
AVERAGE.WEIGHTED accepts a weight of zero, but Google’s page does not say what it does with a blank or text value. For an empty category, the SUMPRODUCT version is the safer choice.
Step 7: Drop the lowest score with MIN or SMALL
To drop the lowest homework score, take it out of the total and divide by one fewer score. In B2:
=(SUM(D2:G2)-MIN(D2:G2))/(COUNT(D2:G2)-1)
(360 − 75) ÷ (4 − 1) = 285 ÷ 3 = 95.00. The weighted grade rises to (95 × 20 + 80 × 30 + 90 × 50) ÷ 100 = 8,800 ÷ 100 = 88.00.
MIN and COUNT both skip empty cells, so a blank in the row is fine. The row needs at least two scores, or the formula divides by zero.
To drop the two lowest, use SMALL. SMALL(D2:G2,1) is the smallest value and SMALL(D2:G2,2) is the next:
=(SUM(D2:G2)-SMALL(D2:G2,1)-SMALL(D2:G2,2))/(COUNT(D2:G2)-2)
(360 − 75 − 90) ÷ 2 = 195 ÷ 2 = 97.50.
The catch comes when items have different point totals. Made-up example. Three quizzes scored 10/10, 17/20 and 17/25. Type the points, and MIN picks 10, the perfect quiz, because it compares raw numbers. Type percents instead (100, 85 and 68), and the formula drops the 68: (100 + 85) ÷ 2 = 92.50. By points, the two quizzes kept are (10 + 17) ÷ (10 + 20) = 27 ÷ 30 = 90.00%.
The Weighted Grade Calculator adds up points inside a category, so with Drop lowest set to 1, these quizzes count as 90%. The simple fix is the one in How to Set Up a Gradebook: give every item in a droppable category the same points.
Step 8: Turn the percent into a letter
Put your scale in a small table, lowest minimum first. This one is a 10-point scale. Use your school’s own grading scale if it differs.
| Row | J | K |
|---|---|---|
| 1 | Min | Letter |
| 2 | 0 | F |
| 3 | 60 | D |
| 4 | 70 | C |
| 5 | 80 | B |
| 6 | 90 | A |
In XLOOKUP, match mode -1 finds an exact match or the next smaller value:
=XLOOKUP(B6,$J$2:$J$6,$K$2:$K$6,"none",-1)
87.00 has no exact match, so it takes the next smaller minimum, 80, and returns B. Microsoft says XLOOKUP is not available in Excel 2016 or Excel 2019. There, VLOOKUP with an approximate match does the same job:
=VLOOKUP(B6,$J$2:$K$6,2,TRUE)
TRUE asks for an approximate match. Microsoft and Google both warn that an approximate match needs the first column sorted, or it can return a wrong value. The $ signs keep the table fixed when you fill the formula down a column of students.
Step 9: Round with ROUND, not with formatting
Formatting a cell to show fewer decimals changes only what you see. Microsoft says Excel calculates with the stored value, not the value shown in the cell.
Made-up example. Another student’s grade is exactly 89.5. Format B6 to show no decimals and it reads 90, but the lookup still sees 89.5 and returns B.
If your syllabus rounds to a whole number, put ROUND inside the lookup:
=VLOOKUP(ROUND(B6,0),$J$2:$K$6,2,TRUE)
ROUND(89.5,0) returns 90, so the letter becomes A. Google’s ROUND page says a next digit of 5 or more rounds up. This matches the calculators with My teacher rounds .5 up checked. Without that box, they read 89.5 as a B, like a sheet with no ROUND.
Step 10: Check your sheet against the Weighted Grade Calculator
Test the sheet with one student before you share grades.
- Open the Weighted Grade Calculator.
- Add Homework with Weight % 20 and Grade so far 90, Quizzes with 30 and 80, and Tests with 50 and 90.
- It shows 87%, the same as B6. Clear the Tests grade and it shows 84%, as in Step 6.
- To check a drop, choose Enter assignments instead in the Homework ⋯ menu, enter the four scores out of 100 and set Drop lowest to 1. The grade shows 88%, as in Step 7.
- For a points-based sheet, enter the same items in the Grade Calculator. 174 out of 200 is 87%.
If the numbers differ, look for a blank counted as zero, a text entry that AVERAGE skipped, or a divisor that includes an empty category. The methodology page explains each rule the calculators use.
Sources
- Microsoft Support: SUMPRODUCT, AVERAGE, COUNT, MIN, SMALL, XLOOKUP, VLOOKUP, ROUND, Stop rounding numbers
- Google Docs Editors Help: SUMPRODUCT, AVERAGE.WEIGHTED, AVERAGE, COUNT, MIN, SMALL, XLOOKUP, VLOOKUP, ROUND
Tools for this guide
- Weighted Grade Calculator: Get your grade when homework, quizzes and exams count differently.
- Grade Calculator: Find your current grade from your assignments.
- Grading Scale: See how percents turn into letter grades, and set your own scale.
More guides
- How to Curve Grades Fairly: Methods Compared
- How to Set Up a Gradebook Students Can Check
- Rubric Grading: Why 2 out of 4 Need Not Mean 50%
- Zero, 50% or Missing: How Each Changes a Grade
- Standards-Based Report Cards: What a 1 to 4 Means
This guide is general information. Your school's rules come first. Found a mistake? Tell us.