Skip to content
Tutorials Center
My learning

Topics

Lesson 87 of 93 ยท Real-World Projects

Project: Gradebook or Employee Attendance Sheet

Build two practical Excel sheets: a gradebook with weighted scores and grade bands, and an attendance tracker using NETWORKDAYS and COUNTIFS.

Teachers, managers and team leads all keep lists of people with numbers next to their names. This project gives you two versions of the same idea, and you can build either or both. The gradebook turns raw scores into a weighted final grade and a letter. The attendance sheet counts the working days in a month, subtracts absences and flags people who need a conversation.

Together they practice four skills: SUMPRODUCT for weighted averages, approximate-match lookups for bands, NETWORKDAYS for working days and COUNTIFS for summaries. Excel cannot run on this site, so formulas appear in plain blocks (type them in Excel) and the TryIts use a small JavaScript spreadsheet engine that evaluates the same formulas as a stand-in.

Two sheets, one toolbox

Gradebook Weighted score, then a letter.
Attendance Workdays minus absences.
Summary Counts per band or status.
1 / 2

Part 1: the gradebook

Layout

Put the weights in their own row so you can change them without editing formulas:

A B C D E F
1 Student Homework Midterm Final Total Grade
2-7 names scores 0-100 scores scores formula formula
10 Weights 30% 30% 40%

Weights must add up to 100%. Add a check cell, for example =SUM(B10:D10), and a conditional format that turns it red if it is not 1.

Weighted total

The weighted score is each score times its weight, added up. You could write =B2*B$10+C2*C$10+D2*D$10, but SUMPRODUCT scales to any number of columns and is easier to audit:

TEXT
E2:  =ROUND(SUMPRODUCT(B2:D2,$B$10:$D$10),1)

The dollar signs lock the weights row while the formula is copied down. See cell references.

Grade bands with an approximate lookup

A long nested IF for letter grades is hard to read and hard to change. A small band table is better. List the lowest score of each band in ascending order:

H I
0 F
60 D
70 C
80 B
90 A

Then look up the total with approximate match:

TEXT
F2:  =VLOOKUP(E2,$H$2:$I$6,2,TRUE)

With TRUE (approximate match) VLOOKUP finds the largest value that is not greater than the one you look for. A total of 78.4 returns C, because 70 is the largest band start at or below it. The table must be sorted ascending, and to change a grading scale you edit the table, not the formulas. In current Excel, XLOOKUP with match mode -1 does the same (XLOOKUP).

Summary

TEXT
Class average:     =AVERAGE(E2:E7)
Number of A's:     =COUNTIF(F2:F7,"A")
Passing (C to B):  =COUNTIFS(E2:E7,">=70",E2:E7,"<90")
Highest total:     =MAX(E2:E7)

COUNTIFS with the same range twice is the standard way to count values between two limits.

Run the gradebook

The engine evaluates the formulas above. It is our stand-in for Excel's calculation, with only the functions this project needs.

JS Weighted grades with a band lookup

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice the edge cases at the bottom: 89.9 is still a B and 90 is exactly an A, because each band starts at its lowest value. Be careful with rounding: if the total is shown as 90.0 but stored as 89.96, the lookup uses the stored number. That is why the total formula rounds with ROUND before the lookup (percentages and rounding).

Part 2: the attendance sheet

Working days in the period

Put the first and last day of the month and the company holidays in labeled cells:

Cell Meaning Value or formula
B1 Month start =DATE(2026,3,1)
B2 Month end =EOMONTH(B1,0)
B3 Holiday list a range such as K2:K4 of dates
B4 Working days =NETWORKDAYS(B1,B2,K2:K4)

NETWORKDAYS(start, end, [holidays]) counts weekdays from Monday to Friday, both ends included, and skips any date in the holiday list. If your team works a different week, use NETWORKDAYS.INTL with a weekend code. See date calculations.

Per-person columns

A B C D E F
Employee Department Absent days Present days Attendance Status
TEXT
D5:  =$B$4-C5
E5:  =ROUND(D5/$B$4,3)
F5:  =IF(E5>=0.95,"Good",IF(E5>=0.9,"Watch","Review"))

The thresholds belong in input cells too, so change 0.95 and 0.9 into references like $N$1 and $N$2 once the sheet works. For many levels, use the same band-table lookup as in the gradebook.

Summary with COUNTIFS

TEXT
Reviews in Sales:   =COUNTIFS(B5:B9,"Sales",F5:F9,"Review")
Average attendance: =AVERAGE(E5:E9)
Reviews overall:    =COUNTIF(F5:F9,"Review")

Run the attendance sheet

The stand-in below adds DATE, EOMONTH and NETWORKDAYS to the same engine. Dates are stored as serial numbers, exactly as Excel does (dates and times).

JS Working days, absences and status

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The holiday on March 17 is a Tuesday, so March 2026 has 22 weekdays but only 21 working days. Forget the holiday list and every percentage in the sheet is a little wrong, which is why the list is a visible input and not a hidden number.

Make it robust

  • Validate scores with data validation: decimal numbers from 0 to 100, and a friendly error message.
  • Blank scores. An empty cell counts as 0 in SUMPRODUCT, so a missing exam looks like a failure. Show "incomplete" with =IF(COUNT(B2:D2)<3,"Incomplete",...).
  • Color the bands with conditional formatting: red for F, green for A, or a fill for Status = Review.
  • Protect the formulas and leave only the score and absence columns unlocked.
  • Keep privacy in mind. Grades and attendance are personal data. Share a protected summary, not the whole file.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does =VLOOKUP(84, $H$2:$I$6, 2, TRUE) return with the bands 0 F, 60 D, 70 C, 80 B, 90 A?

    Approximate match returns the row with the largest start value that is not greater than 84, which is 80, so the grade is B.
  2. 2. Why use SUMPRODUCT(B2:D2,$B$10:$D$10) for a weighted total?

    SUMPRODUCT pairs up the two ranges, multiplies the pairs and adds the products, which is exactly a weighted sum.
  3. 3. Which function counts Monday-to-Friday days between two dates and can skip a list of holidays?

    NETWORKDAYS counts working days between two dates, inclusive, and accepts a holiday range. WORKDAY returns a date instead.
  4. 4. How do you count scores that are at least 70 and below 90?

    COUNTIFS takes several range and criteria pairs, and all of them must match.
  5. 5. Why should the total be rounded with ROUND before the grade lookup?

    A display format only hides digits. A stored 89.96 still looks up as a B even if it is shown as 90.0.

Summary

  • Put weights, grade bands, thresholds and holidays in labeled input cells, not inside formulas.
  • SUMPRODUCT gives a weighted total and an ascending band table with approximate match gives letters.
  • NETWORKDAYS counts working days and skips holidays; subtract absences for present days.
  • COUNTIF and COUNTIFS build the summaries, even with two conditions on the same range.
  • The JavaScript engine is a stand-in that runs these formulas; the formulas themselves are what you type in Excel.

Next: Formula Exercises with Solutions.