Grade Calculator in Excel: Averages, Weights and Letter Grades
Build a grade calculator in Excel: weighted scores with SUMPRODUCT, letter grades with IFS or a lookup table, class statistics, and pass/fail counts.
By Ayoub Farid · · 1 min read
A grade calculator is a great practice project for students and teachers, and a classic job-test task. It uses weighted averages, IF logic, lookups and counting.
Step 1: the scores table
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Homework | Midterm | Final | Total |
| 2 | Ana | 88 | 76 | 82 | |
| 3 | Ben | 95 | 89 | 91 |
Weights in a row above or beside it, e.g. G2:I2 = 20%, 30%, 50%.
Step 2: weighted total
In E2:
=SUMPRODUCT(B2:D2,$G$2:$I$2)Lock the weights so every student uses the same ones. Ana: 88×0.2 + 76×0.3 + 82×0.5 = 81.4. See weighted average.
Step 3: letter grade
With IFS:
=IFS(E2>=90,"A",E2>=80,"B",E2>=70,"C",E2>=60,"D",TRUE,"F")Or with a lookup table (easier to change):
| K | L | |
|---|---|---|
| 1 | Min | Grade |
| 2 | 0 | F |
| 3 | 60 | D |
| 4 | 70 | C |
| 5 | 80 | B |
| 6 | 90 | A |
=VLOOKUP(E2,$K$2:$L$6,2,TRUE)Step 4: pass or fail
=IF(E2>=60,"Pass","Fail")Step 5: class statistics
- Class average:
=AVERAGE(E2:E31) - Median:
=MEDIAN(E2:E31) - Highest / lowest:
=MAX(E2:E31)/=MIN(E2:E31) - Number of A grades:
=COUNTIF(F2:F31,"A") - Pass rate:
=COUNTIF(G2:G31,"Pass")/COUNTA(G2:G31) - Rank:
=RANK.EQ(E2,$E$2:$E$31)(see RANK)
Missing scores
A blank score counts as 0 in SUMPRODUCT. Flag incomplete rows: =IF(COUNT(B2:D2)<3,"Incomplete",...).
Frequently asked questions
How do I calculate a weighted grade in Excel?
=SUMPRODUCT(scores,weights), with weights adding up to 100%.
How do I convert scores to letter grades?
Use IFS, nested IF, or VLOOKUP with approximate match on a threshold table.
How do I calculate a class average?
=AVERAGE(range) of the total column.
Does this work in Google Sheets?
Yes.