Repository Public Research preview
1# Grading a workbook against a reference23The grader compares cells mechanically and returns evidence with suggested points. It is an aid:4a person reads the evidence and decides the mark.56## Preconditions78- The submission keeps the reference layout: same sheet names, same cells. Say so in the9 assignment and hand out a template with the labels in place. A submission built on another10 layout comes back mostly `missing`: grade it by hand.11- Both files hold stored results. A submission saved by Excel always does. If12 `recalc` says `no_cached_values`, recalculate it with LibreOffice first.13- The rubric exists before any submission is opened.1415## Rubric format1617```json18{19 "tolerance": 0.005,20 "rel_tolerance": 0,21 "formula_required": false,22 "constant_credit": 0.5,23 "carried_forward_credit": 1,24 "partial": "proportional",25 "items": [26 { "id": "revenue", "label": "Revenue", "range": "Calc!B3:F3", "points": 5 },27 { "id": "units", "range": "Calc!C2:F2", "points": 4, "formula_required": true },28 { "id": "summary", "ranges": ["Outputs!B1", "Outputs!B2"], "points": 2, "partial": "all_or_nothing" }29 ]30}31```3233| Field | Where | Default | Meaning |34|---|---|---|---|35| `items[].range` / `ranges` | item | required | Cells graded, with the sheet name. Only cells that are non-empty in the reference count |36| `items[].points` | item | required | Points for the item |37| `id`, `label` | item | `item-N`, none | Shown in the evidence |38| `tolerance` | top or item | 0.005 | Absolute tolerance on numbers |39| `rel_tolerance` | top or item | 0 | Relative tolerance, combined with the absolute one (the larger band applies) |40| `formula_required` | top or item | false | A typed number earns only `constant_credit` |41| `constant_credit` | top or item | 0.5 | Credit (0 to 1) for a right value typed where a formula is required |42| `carried_forward_credit` | top or item | 1 | Credit (0 to 1) for a wrong value produced by the right method |43| `partial` | top or item | `proportional` | `proportional`: mean cell credit × points. `all_or_nothing`: points only if every cell has credit 1 |4445Text answers are compared without case or surrounding spaces; booleans exactly; an error value46matches only the same error.4748## How each cell is judged49501. The expected value is the reference cell's value; the submitted value is the submission's.51 Both follow the value rule (stored result, else evaluated within the subset, else unknown).522. Equal within tolerance → `correct`, or `correct_value_typed_as_constant` when the item53 requires a formula, the reference cell holds one, and the submission holds a typed value.543. Not equal, and the submission holds a formula → the **reference formula is evaluated on the55 submission's own values**. If that gives the submitted value, the method is right and the56 error comes from an earlier cell: `carried_forward_error`. An equivalent formula written57 differently (`=SUM(B5:F5)/5` for `=AVERAGE(B5:F5)`) passes this test too.584. When the reference formula is outside the evaluated subset, the test falls back to "same59 formula pattern as the reference", and the note says it was not recomputed.605. Same formula as the reference but a stored value that does not follow from it → `wrong`, with61 a note: the stored value is stale or was overwritten. Recalculate the file and grade again.626. Otherwise `wrong`. The note flags a sign error (right absolute value) or a scale error63 (expected × 100, 0.01, 1000, 0.001, 12, 1/12).647. Empty cell or missing sheet → `missing`. Value not available without Excel → `unknown`,65 credit 0, listed in `needs_human_review`.6667## Policy choices, and their consequences6869**Carried-forward credit.** Full credit (1) penalises each mistake once, which is the usual70intent when later questions test other skills. It requires that the originating cells are rubric71items. If the first error sits in a cell no item covers, the student loses nothing: the tool72warns whenever it grants carried-forward credit. Set 0.5 or 0 for a summary item where the final73figure itself matters.7475**Formula required.** Use it where building the formula is the skill assessed, and announce it.76A typed right answer may come from a calculator or from a neighbour; partial credit (0.5) is a77common compromise, 0 is defensible if announced. Do not require formulas for cells where typing78is legitimate (inputs copied from the statement).7980**Tolerance.** Half the last unit shown: 0.005 for amounts in cents, 0.5 for whole units,810.00005 for a rate shown with four decimals. A wider band rewards answers that are merely close.82Intermediate rounding differences along a long chain are better handled by `rel_tolerance`83(for example 1e-6) than by a wide absolute band.8485**All-or-nothing items.** Useful for a final answer that must be exactly right. Combine with86`carried_forward_credit` below 1 if an upstream error should cost the final answer.8788**Changed inputs.** `inputs_changed` lists every reference input that differs in the submission.89If the data were given, a changed input is either a transcription error (penalise once, by hand)90or an attempt to make numbers tie. Every downstream cell then shows as carried forward from a91cell no item covers, so look at this list before accepting the total.9293## Reading the result9495```bash96node scripts/xlsx.mjs grade reference.xlsx student.xlsx --rubric rubric.json --out evidence.md97```9899- `suggested_total`, `max_points`, `percent`.100- `items[].evidence[]`: per cell `verdict`, `credit`, `expected`, `got`, both formulas, `note`.101- `verdict_counts`: a quick profile of the submission.102- `needs_human_review`, `inputs_changed`, `structure` (missing or added sheets, names, differing103 used ranges), `warnings`.104- `disclaimer`: always present; keep it in anything shown to the student.105106The Markdown evidence lists, per item, only the cells that are not fully correct.107108## Batch grading109110```bash111mkdir -p evidence112for f in submissions/*.xlsx; do113 id=$(basename "$f" .xlsx)114 node scripts/xlsx.mjs grade reference.xlsx "$f" --rubric rubric.json --out "evidence/$id.json" > /dev/null \115 || echo "$id: could not be graded automatically"116done117```118119- One rubric for the whole batch. If the rubric must change after grading started, regrade120 everyone into a new evidence folder (existing evidence files are never overwritten).121- A file that fails to open (`not_a_zip`, `unsupported_type`, `zip_encrypted`) is a submission122 to handle by hand, not a zero.123- Before releasing marks, read the evidence of the lowest and highest totals and of every124 submission with `needs_human_review`, `inputs_changed` or structural differences.125- Keep submissions and evidence out of shared or public folders: they are personal records.126127## What the grader cannot see128129- Whether a different but valid approach was used on another layout.130- Explanations, labels, formatting, charts, comments.131- Whether a formula is *right for the right reason*: a formula that gives the reference value on132 this data set is counted correct. `compare` reports `different_formula_same_value`; read those133 cells when the count is high, or grade against a second reference built on other inputs.134- Formulas outside the evaluated subset when the file has no stored results.135