Build a weighted gradebook spreadsheet

You are an experienced teacher and spreadsheet builder. A gradebook is a policy written in formulas: students and parents will challenge any grade, so every number must be reproducible by hand from the syllabus. The common errors are well known: treating "not yet graded" as zero, dropping the lowest raw score instead of the lowest percentage, weights that silently stop adding to 100% mid-term, and late penalties that push scores below zero.

Build a gradebook in google-sheets for this course.

1. List the policy choices the formulas depend on, with the default you will use if the teacher has not said:

• Within a category, total points (sum earned over sum possible) or equal-weight average of percentages. Default: total points, unless items have very different point values and the syllabus says each counts equally.
• Blank means not yet graded or excused, and is excluded; 0 means missing work. Use a code such as EX for excused.
• Running grade: renormalise weights over categories that have graded work so far, so an early-term grade is not deflated by empty categories.
• Dropping: drop the item with the lowest percentage, removing both its earned and possible points; never drop more items than were graded.
• Late penalty: applied to the earned score as a percentage of possible points per day late after any grace period, capped, and never below zero.
• Boundaries: whether to round the final percentage before the letter lookup. If the weights do not add to 100%, stop and ask.

1. Layout: a Settings sheet (categories, weights, drops, late rule, grade scale table sorted ascending), an Assignments sheet (ID, name, category, points possible, due date), and a Scores sheet with one row per student and one column per assignment. Late submissions: a matching Submitted-date block, or a days-late block, whichever is simpler for the teacher; say which.
2. Formulas, each for one student row, referencing Settings by named ranges rather than typed numbers:

• Adjusted score per item after the late penalty.
• Category earned and possible with SUMIFS-style logic over the assignment header row, skipping blanks and EX.
• Drop lowest: in Microsoft 365 or Google Sheets, use LET with FILTER and a sort by percentage to keep all but the lowest k items: SORTBY in Excel, SORT with the percentage array as its sort column in Google Sheets (which has no SORTBY). Give an older-Excel fallback for dropping one item (subtract the item whose percentage equals the minimum, using a helper row of percentages).
• Category percentage, weighted final percentage with renormalised weights, and the letter grade with XLOOKUP in next-smaller match mode or VLOOKUP with approximate match on the ascending scale.

1. Work one fictional student through by hand, showing each step, so the teacher can verify the sheet against it.

• Use only functions available in google-sheets, with comma separators, and note once that some locales use semicolons.
• No numbers typed into formulas that live in Settings (weights, penalties, cut-offs, number of drops).
• Use invented student names only in the worked example; remind the teacher not to paste real student records into an AI chat.
• Keep formulas readable: use LET where it helps and helper rows rather than one unreadable formula.
• If the policy text is ambiguous (for example "drop the lowest quiz" when quizzes have different point values), state the interpretation you used and how to switch.

Policy choices to confirm

Table: Choice | What the formula does | Change it by.

Workbook layout

Each sheet with its columns and the named ranges.

Formulas

Table: Purpose | Cell | Formula | Notes. Formulas in code formatting, ready to paste.

Worked check

One fictional student, category by category, ending in the final percentage and letter.

Maintenance

Three to five bullets: adding an assignment, excusing a student, changing a weight mid-term, protecting formula cells.