Lessons Lesson 102

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

By Ali, Founder, Excel Lessons

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.

Read more about this

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, type:

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

This fills down to E6.

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, type: =ROUND(AVERAGE(B2:D2),1). This fills down to E6.

    Hint. AVERAGE the tests, then ROUND.

  2. 2

    In F2, find Ada's grade in H2:I5 with VLOOKUP. Shape: =VLOOKUP(value, table, col, TRUE). Lock the table so it doesn't move.

    Hint. TRUE, for the band.

  3. 3

    F3 used an exact match and shows #N/A. No boundary equals 51.3 exactly. Fix F3.

    Hint. The last argument.

  4. 4

    In B8, count the students who got an A with COUNTIF.

    Hint. "A" in quotation marks.

  5. 5

    In B10, average the marks of students who got an A with AVERAGEIF.

    Hint. Grade column, "A", then marks.

  6. 6

    This is the boundary case a student will argue about. Read =VLOOKUP(80,$H$2:$I$5,2,TRUE) and say the grade for 80.

    Hint. The band that starts at 80 takes 80.