Defect catalogue
Common defects of model workbooks, how each one shows, the command that reveals it, and the fix.
Commands are those of scripts/xlsx.mjs.
Structure
| Defect | How it shows | Command | Fix |
|---|---|---|---|
| Inputs scattered through calculation sheets | Typed numbers reached in the middle of a trace (input: true on a Calc sheet) |
precedents on an output, summary (numbers per sheet) |
Move each one to the input sheet, link back |
| Assumption buried in a formula | Changing the input sheet does not change every period | hardcoded (constants inside formulas) |
Replace the number with a reference to a labelled input |
| Calculation on the output sheet | Output cells with long formulas | formulas --sheet Outputs --group |
Move the logic to Calc; outputs only reference |
| Hidden sheet with live logic | state: hidden and a non-zero formula count |
summary |
Unhide, document, or remove |
| Links to other workbooks | has_external_links: true; formulas outside the subset with reason "external workbook" |
summary, recalc |
Import the values as documented inputs |
| No documentation, no version | No Doc sheet; nobody can tell which file was reviewed | summary |
Add a Doc sheet with a version stamp |
Formulas
| Defect | How it shows | Command | Fix |
|---|---|---|---|
| Formula overwritten by a typed number | One period does not move with the inputs | hardcoded (typed constants where a formula is expected) |
Restore expected_formula, after comparing its result with the typed value |
| One cell differs from its row | A single odd period; totals slightly off | consistency (high confidence) |
Copy the row formula over it |
| Wrong absolute / relative reference | Pattern breaks from the second copied cell on | consistency, formulas --group (many patterns in one row) |
Fix the $, recopy |
| Range that stops short | A total ignoring the last rows | precedents on the total (count the level-1 cells) |
Extend the range; prefer a total row directly under the data |
| Total that includes itself | Circular reference of one cell | circular |
Fix the range |
| Double counting | A subtotal and its details in the same sum | precedents on the total |
Sum details or subtotals, not both |
| Sign error | Costs added; negative margin | formulas --group; grade notes "Sign error" |
Apply the documented sign convention |
| Scale error | Result 100 or 1000 times off; a rate stored as 5 instead of 0.05 | grade notes "Scale error"; range on the inputs |
Store rates as decimals; convert units in one labelled place |
| Rounding inside the chain | Totals differ from the sum of displayed lines | formulas --group (look for ROUND) |
Round at the end, or round every line and sum the rounded lines, consistently |
Error hidden by IFERROR |
Blank or zero where a figure should be | formulas --group (patterns starting with IFERROR) |
Test the specific condition instead |
| Volatile or indirect references | OFFSET, INDIRECT in functions_used; traces stop short |
summary |
Replace with direct references or INDEX |
Errors
| Defect | How it shows | Command | Fix |
|---|---|---|---|
| Deleted row, column or sheet | #REF!, broken_reference: true |
errors (root causes) |
Rebuild the reference |
| Empty or zero denominator | #DIV/0! |
errors, then precedents on the root |
Guard the case explicitly |
| Failed lookup | #N/A |
errors |
Fix key or table |
| Number stored as text | #VALUE!, or a sum that ignores a cell |
errors; range (type string on a numeric column) |
Convert the cell to a number |
| Error trail | Many errors, one cause | errors (propagated_from), dependents on the root |
Fix the root only |
Controls
| Defect | How it shows | Command | Fix |
|---|---|---|---|
| No controls | found: false |
controls |
Add reconciliations and an overall cell |
| Check typed by hand | typed_constant: true |
controls |
Replace with a formula |
| Overall cell not covering every row | overall_cell_consistent: false |
controls |
Extend its range |
| Control comparing a figure with itself | Left and right trace to the same cells | precedents on both sides |
Compare two independent routes |
| Tolerance typed in the formula | Constant in the control formula | hardcoded |
Make the tolerance an input |
| Failing control ignored | failing_controls non-empty in a delivered file |
controls |
Find the cause with precedents on the difference |
File state
| Defect | How it shows | Command | Fix |
|---|---|---|---|
| Written by a script, never calculated | no_cached_values; values unknown |
recalc, summary |
Open and save in Excel, or soffice --headless --convert-to xlsx |
| Stale results (manual calculation mode, or edited outside Excel) | stale_or_altered_values with the cells listed |
recalc |
Recalculate; if mismatches persist, the stored values were edited |
| Circular reference with iterative calculation | Loop listed; values change at each recalculation | circular |
Break the loop explicitly |
| Macros present | has_macros: true |
summary |
Review the macros separately; this tool never reads them |
Submissions (when grading)
| Situation | How it shows | Where |
|---|---|---|
| Right value typed instead of computed | correct_value_typed_as_constant |
grade evidence |
| Right method on a wrong earlier value | carried_forward_error |
grade evidence |
| Given data altered | inputs_changed |
grade, compare |
| Equivalent formula | correct, same_formula: false |
grade evidence |
| Different layout | Many missing; structure.sheets_missing |
compare |
| Stored value not matching its formula | wrong with the note "stored value does not follow" |
grade evidence; confirm with recalc |