SPB Git

Repository Public Research preview

defects.md 5.7 KB 73 lines markdown

1# Defect catalogue23Common defects of model workbooks, how each one shows, the command that reveals it, and the fix.4Commands are those of `scripts/xlsx.mjs`.56## Structure78| Defect | How it shows | Command | Fix |9|---|---|---|---|10| 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 |11| 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 |12| Calculation on the output sheet | Output cells with long formulas | `formulas --sheet Outputs --group` | Move the logic to Calc; outputs only reference |13| Hidden sheet with live logic | `state: hidden` and a non-zero formula count | `summary` | Unhide, document, or remove |14| Links to other workbooks | `has_external_links: true`; formulas outside the subset with reason "external workbook" | `summary`, `recalc` | Import the values as documented inputs |15| No documentation, no version | No Doc sheet; nobody can tell which file was reviewed | `summary` | Add a Doc sheet with a version stamp |1617## Formulas1819| Defect | How it shows | Command | Fix |20|---|---|---|---|21| 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 |22| One cell differs from its row | A single odd period; totals slightly off | `consistency` (`high` confidence) | Copy the row formula over it |23| Wrong absolute / relative reference | Pattern breaks from the second copied cell on | `consistency`, `formulas --group` (many patterns in one row) | Fix the `$`, recopy |24| 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 |25| Total that includes itself | Circular reference of one cell | `circular` | Fix the range |26| Double counting | A subtotal and its details in the same sum | `precedents` on the total | Sum details or subtotals, not both |27| Sign error | Costs added; negative margin | `formulas --group`; `grade` notes "Sign error" | Apply the documented sign convention |28| 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 |29| 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 |30| Error hidden by `IFERROR` | Blank or zero where a figure should be | `formulas --group` (patterns starting with `IFERROR`) | Test the specific condition instead |31| Volatile or indirect references | `OFFSET`, `INDIRECT` in `functions_used`; traces stop short | `summary` | Replace with direct references or `INDEX` |3233## Errors3435| Defect | How it shows | Command | Fix |36|---|---|---|---|37| Deleted row, column or sheet | `#REF!`, `broken_reference: true` | `errors` (root causes) | Rebuild the reference |38| Empty or zero denominator | `#DIV/0!` | `errors`, then `precedents` on the root | Guard the case explicitly |39| Failed lookup | `#N/A` | `errors` | Fix key or table |40| 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 |41| Error trail | Many errors, one cause | `errors` (`propagated_from`), `dependents` on the root | Fix the root only |4243## Controls4445| Defect | How it shows | Command | Fix |46|---|---|---|---|47| No controls | `found: false` | `controls` | Add reconciliations and an overall cell |48| Check typed by hand | `typed_constant: true` | `controls` | Replace with a formula |49| Overall cell not covering every row | `overall_cell_consistent: false` | `controls` | Extend its range |50| Control comparing a figure with itself | Left and right trace to the same cells | `precedents` on both sides | Compare two independent routes |51| Tolerance typed in the formula | Constant in the control formula | `hardcoded` | Make the tolerance an input |52| Failing control ignored | `failing_controls` non-empty in a delivered file | `controls` | Find the cause with `precedents` on the difference |5354## File state5556| Defect | How it shows | Command | Fix |57|---|---|---|---|58| Written by a script, never calculated | `no_cached_values`; values `unknown` | `recalc`, `summary` | Open and save in Excel, or `soffice --headless --convert-to xlsx` |59| 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 |60| Circular reference with iterative calculation | Loop listed; values change at each recalculation | `circular` | Break the loop explicitly |61| Macros present | `has_macros: true` | `summary` | Review the macros separately; this tool never reads them |6263## Submissions (when grading)6465| Situation | How it shows | Where |66|---|---|---|67| Right value typed instead of computed | `correct_value_typed_as_constant` | `grade` evidence |68| Right method on a wrong earlier value | `carried_forward_error` | `grade` evidence |69| Given data altered | `inputs_changed` | `grade`, `compare` |70| Equivalent formula | `correct`, `same_formula: false` | `grade` evidence |71| Different layout | Many `missing`; `structure.sheets_missing` | `compare` |72| Stored value not matching its formula | `wrong` with the note "stored value does not follow" | `grade` evidence; confirm with `recalc` |73
Files main
Menu

Theme