SPB Git

Repository Public Research preview

defects.md 5.7 KB

# 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
Files main
Menu

Theme