Course outline / Advanced Excel
Lesson 66 of 75 · Intermediate · 4 exercises
Building a grade book
Weighted marks, letter grades and class statistics.
What you'll practise
- Final mark
- Letter grade
- Class average
- Pass rate
Final mark
The final mark weights coursework by the value in G1 and the exam by the rest.
**In cell E2, work out Ana's final mark: B2*$G$1 + C2*(1-$G$1).**
Letter grade
In cell E2, give Ben a letter grade for his exam (C3) with IFS: "A" from 80, "B" from 65, "C" from 50, otherwise "F".
Class average
In cell E2, find the class average exam mark (C2:C6), rounded to 1 decimal place.
Pass rate
In cell E2, work out the share of students who scored 50 or more in the exam: COUNTIF(C2:C6,">=50") divided by COUNT(C2:C6).
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.