Course / Challenges

Challenge: the class gradebook

Turn raw marks into averages, letter grades, a class ranking and a pass rate. Intermediate · 8 min · ROUND, AVERAGE, Approximate lookup, RANK, COUNTIF

You're the form tutor for a class of fifteen. Each student has four marks out of 100, and the school wants a results sheet: an average, a letter grade, a class ranking and the pass rate.

The grade boundaries are in J1:K6 and the pass mark is in K8. Point your formulas at those cells instead of typing the numbers in, so the sheet keeps working if the school changes its rules.

> Challenges give you the goal, not the formula. Stuck? Each hint is more specific than the last, and the final one gives the answer.

Steps

1. In F2:F16, work out each student's average of the four marks, rounded to a whole number. Use rounding in the formula, not just a number format, so the grades and ranks use the same figure the report card shows.

2. In G2:G16, give each student a letter grade by looking their average up in the grade table J1:K6. An average of 78 should get a C: it is at least 70 but below 80.

3. In H2:H16, rank the students by average, so the highest average is ranked 1.

4. In K9, count how many students have an average at or above the pass mark in K8.

5. In K10, show the pass rate: the share of the class that passed, formatted as a percentage.

Remember

• ROUND(AVERAGE(…), 0) stores the rounded value, so later formulas use it too.
• An approximate lookup against a boundaries table turns scores into grades.
• RANK needs a locked list; COUNTIF can compare against a cell with ">="&K8.
• Keep rules like pass marks in cells, never inside formulas.

Previous: Formatting and finishing touches · Next: Challenge: quarterly sales summary