Guided project · 5 steps Cellwise Plus
Class gradebook in Excel
Weight assignments, assign letter grades, and see how the class did.
You will use: SUMPRODUCT, IFS, AVERAGE, ROUND, COUNTIF, MAX, XLOOKUP
Starting data
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Student | Homework | Quizzes | Final | Grade | Letter | Weights | 0.3 | 0.3 | 0.4 | |
| 2 | Ava | 95 | 88 | 91 | |||||||
| 3 | Ben | 72 | 80 | 68 | Class average | ||||||
| 4 | Cara | 88 | 92 | 94 | Passing (70+) | ||||||
| 5 | Dev | 60 | 71 | 74 | Top student | ||||||
| 6 | Eli | 99 | 95 | 97 | |||||||
| 7 | Finn | 81 | 77 | 85 |
Build it step by step
Step 1
Weights are in I1:K1. Write the weighted grade in E2, fill to E7.
Step 2
Give a letter in F2: 90+ A, 80+ B, 70+ C, else D. Fill to F7.
Step 3
What is the class average, rounded to one decimal? Put it in I3.
Step 4
How many students are passing with 70 or more? Put it in I4.
Step 5
Who has the top grade? Put the name in I5.