Project: a grade book
From: Ms. Adebayo, science teacher at a fictional high school
About: End-of-term grades
I have four scores for each of my twelve students. I need each student's average, their letter grade from our scale (in J2:K6), their rank in the class, and a short summary: class average, how many passed, who came top and how many earned an A.
Please use formulas: I will reuse the sheet next term with new scores.
A grade book is a classic first Excel project, and it uses a function many people get wrong: VLOOKUP with an approximate match.
When the lookup value falls between rows of a table (an 84.5 average against a scale of 80 = B, 90 = A), the fourth argument TRUE finds the largest value in the first column that isn't above it. The table must be sorted from low to high for that to work. FALSE, the exact match you use for IDs, would return #N/A here.
The rest is familiar: AVERAGE across a row, RANK.EQ with a locked list, COUNTIF and INDEX with MATCH.
=VLOOKUP(F2,$J$2:$K$6,2,TRUE)
An approximate-match lookup into a grade scale sorted from low to high.