Grading a workbook against a reference
The grader compares cells mechanically and returns evidence with suggested points. It is an aid: a person reads the evidence and decides the mark.
Preconditions
- The submission keeps the reference layout: same sheet names, same cells. Say so in the
assignment and hand out a template with the labels in place. A submission built on another
layout comes back mostly
missing: grade it by hand. - Both files hold stored results. A submission saved by Excel always does. If
recalcsaysno_cached_values, recalculate it with LibreOffice first. - The rubric exists before any submission is opened.
Rubric format
{
"tolerance": 0.005,
"rel_tolerance": 0,
"formula_required": false,
"constant_credit": 0.5,
"carried_forward_credit": 1,
"partial": "proportional",
"items": [
{ "id": "revenue", "label": "Revenue", "range": "Calc!B3:F3", "points": 5 },
{ "id": "units", "range": "Calc!C2:F2", "points": 4, "formula_required": true },
{ "id": "summary", "ranges": ["Outputs!B1", "Outputs!B2"], "points": 2, "partial": "all_or_nothing" }
]
}| Field | Where | Default | Meaning |
|---|---|---|---|
items[].range / ranges |
item | required | Cells graded, with the sheet name. Only cells that are non-empty in the reference count |
items[].points |
item | required | Points for the item |
id, label |
item | item-N, none |
Shown in the evidence |
tolerance |
top or item | 0.005 | Absolute tolerance on numbers |
rel_tolerance |
top or item | 0 | Relative tolerance, combined with the absolute one (the larger band applies) |
formula_required |
top or item | false | A typed number earns only constant_credit |
constant_credit |
top or item | 0.5 | Credit (0 to 1) for a right value typed where a formula is required |
carried_forward_credit |
top or item | 1 | Credit (0 to 1) for a wrong value produced by the right method |
partial |
top or item | proportional |
proportional: mean cell credit × points. all_or_nothing: points only if every cell has credit 1 |
Text answers are compared without case or surrounding spaces; booleans exactly; an error value matches only the same error.
How each cell is judged
- The expected value is the reference cell's value; the submitted value is the submission's. Both follow the value rule (stored result, else evaluated within the subset, else unknown).
- Equal within tolerance →
correct, orcorrect_value_typed_as_constantwhen the item requires a formula, the reference cell holds one, and the submission holds a typed value. - Not equal, and the submission holds a formula → the reference formula is evaluated on the
submission's own values. If that gives the submitted value, the method is right and the
error comes from an earlier cell:
carried_forward_error. An equivalent formula written differently (=SUM(B5:F5)/5for=AVERAGE(B5:F5)) passes this test too. - When the reference formula is outside the evaluated subset, the test falls back to "same formula pattern as the reference", and the note says it was not recomputed.
- Same formula as the reference but a stored value that does not follow from it →
wrong, with a note: the stored value is stale or was overwritten. Recalculate the file and grade again. - Otherwise
wrong. The note flags a sign error (right absolute value) or a scale error (expected × 100, 0.01, 1000, 0.001, 12, 1/12). - Empty cell or missing sheet →
missing. Value not available without Excel →unknown, credit 0, listed inneeds_human_review.
Policy choices, and their consequences
Carried-forward credit. Full credit (1) penalises each mistake once, which is the usual intent when later questions test other skills. It requires that the originating cells are rubric items. If the first error sits in a cell no item covers, the student loses nothing: the tool warns whenever it grants carried-forward credit. Set 0.5 or 0 for a summary item where the final figure itself matters.
Formula required. Use it where building the formula is the skill assessed, and announce it. A typed right answer may come from a calculator or from a neighbour; partial credit (0.5) is a common compromise, 0 is defensible if announced. Do not require formulas for cells where typing is legitimate (inputs copied from the statement).
Tolerance. Half the last unit shown: 0.005 for amounts in cents, 0.5 for whole units,
0.00005 for a rate shown with four decimals. A wider band rewards answers that are merely close.
Intermediate rounding differences along a long chain are better handled by rel_tolerance
(for example 1e-6) than by a wide absolute band.
All-or-nothing items. Useful for a final answer that must be exactly right. Combine with
carried_forward_credit below 1 if an upstream error should cost the final answer.
Changed inputs. inputs_changed lists every reference input that differs in the submission.
If the data were given, a changed input is either a transcription error (penalise once, by hand)
or an attempt to make numbers tie. Every downstream cell then shows as carried forward from a
cell no item covers, so look at this list before accepting the total.
Reading the result
node scripts/xlsx.mjs grade reference.xlsx student.xlsx --rubric rubric.json --out evidence.mdsuggested_total,max_points,percent.items[].evidence[]: per cellverdict,credit,expected,got, both formulas,note.verdict_counts: a quick profile of the submission.needs_human_review,inputs_changed,structure(missing or added sheets, names, differing used ranges),warnings.disclaimer: always present; keep it in anything shown to the student.
The Markdown evidence lists, per item, only the cells that are not fully correct.
Batch grading
mkdir -p evidence
for f in submissions/*.xlsx; do
id=$(basename "$f" .xlsx)
node scripts/xlsx.mjs grade reference.xlsx "$f" --rubric rubric.json --out "evidence/$id.json" > /dev/null \
|| echo "$id: could not be graded automatically"
done- One rubric for the whole batch. If the rubric must change after grading started, regrade everyone into a new evidence folder (existing evidence files are never overwritten).
- A file that fails to open (
not_a_zip,unsupported_type,zip_encrypted) is a submission to handle by hand, not a zero. - Before releasing marks, read the evidence of the lowest and highest totals and of every
submission with
needs_human_review,inputs_changedor structural differences. - Keep submissions and evidence out of shared or public folders: they are personal records.
What the grader cannot see
- Whether a different but valid approach was used on another layout.
- Explanations, labels, formatting, charts, comments.
- Whether a formula is right for the right reason: a formula that gives the reference value on
this data set is counted correct.
comparereportsdifferent_formula_same_value; read those cells when the count is high, or grade against a second reference built on other inputs. - Formulas outside the evaluated subset when the file has no stored results.