Excel Practice
Lessons Lesson 100

Project: A Grade Book

About this lesson

A grade book is an average rounded as a value, and a letter grade from a boundary table by approximate match. A summary block of COUNTIF and AVERAGEIF reads those two columns.

By the end you can

  • turn marks into letter grades with a boundary table.
  • summarise a class with COUNTIF, INDEX MATCH and AVERAGEIF.

Practises AVERAGE ROUND VLOOKUP COUNTIF AVERAGEIF

The idea

A teacher's sheet, and the second project because it needs the band lookup. The average is rounded with ROUND, so the printed mark is the graded mark. The grade is a VLOOKUP with TRUE against the boundaries. The summary reads the two columns those produce.

The mistake is step 3's: an exact match against a band table, which finds nobody. The boundary table lists where each grade starts. Starts are found by approximate match, on this sheet and on every tax table and shipping-rate card there is.

The mistake to watch for

An exact match against a band table. FALSE finds only a student whose average is exactly 50, 65 or 80, and shows #N/A for everyone else. Boundaries are found with TRUE. A mark equal to a boundary belongs to the band that starts there. So round the average as a value first, so the printed mark and the graded mark agree.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In E2, Ada's average over the three tests, rounded to one decimal place. This fills down to E6.

To begin, type it exactly:

=ROUND(AVERAGE(B2:D2),1)

E2
Row A B C D E F G H I
1 Student Test 1 Test 2 Test 3 Average Grade From Grade
2 Ada 72 65 80 0 F
3 Ben 48 55 51 50 C
4 Cai 91 88 95 65 B
5 Dee 60 70 64 80 A
6 Eli 83 79 85
7
8 A grades
9
10 A-grade average

Every step

  1. 1

    In E2, Ada's average over the three tests, rounded to one decimal place. This fills down to E6. To begin, type it exactly: =ROUND(AVERAGE(B2:D2),1).

    Hint. ROUND outside, AVERAGE inside.

  2. 2

    In F2, Ada's grade from the boundaries in H2:I5, with an approximate VLOOKUP. Lock the table.

    Hint. TRUE, for the band.

  3. 3

    F3 was written with an exact match and shows #N/A, because no boundary is exactly 51.3. Fix it.

    Hint. The last argument.

  4. 4

    In B8, how many students got an A.

    Hint. "A" in quotation marks.

  5. 5

    In B10, write a formula using AVERAGEIF that gives the average mark of the students who got an A.

    Hint. Grades, "A", averages.

  6. 6

    The boundary case, which is the one a student will argue about. Read =VLOOKUP(80,$H$2:$I$5,2,TRUE) and say which grade a mark of exactly 80 gets.

    Hint. From 80, inclusive.