Intermediate
Class Grade Book
Track student scores across several assignments and calculate a final average and letter grade.
Skills You'll Use
AVERAGE function
IF function (nested)
Conditional Formatting
Instructions
Create a sheet with a row per student and columns for each assignment score, plus a Final Average column:
=AVERAGE(B2:E2)
Then add a Letter Grade column using nested IF statements:
=IF(F2>=90,"A",IF(F2>=80,"B",IF(F2>=70,"C","F")))
Finally, apply Conditional Formatting to the Final Average column to highlight any student below 70 in red, so struggling students are easy to spot at a glance.
Sample Data
| Student | Quiz 1 | Quiz 2 | Quiz 3 | Quiz 4 |
|---|---|---|---|---|
| Alex | 85 | 90 | 78 | 92 |
| Jordan | 65 | 70 | 60 | 72 |
| Sam | 95 | 98 | 91 | 89 |
Challenge
Add a class average row at the bottom that averages every student's Final Average column, so you can see how the whole class performed.
Expected Result
A grade book that automatically calculates each student's average and letter grade, with underperforming students visually flagged.