Spreadsheet Unit Audit¶

Spreadsheet audit procedure — instantiates Dimensional Consistency Check

Walks an actual spreadsheet cell by cell — columns, hidden intermediate cells, and formula chains — to surface the unlabeled unit and denominator slips that spreadsheets breed.

Spreadsheet Unit Audit is the check applied to the artifact where dimensional errors most love to hide: a live spreadsheet. Its defining feature is that it works on the real, messy object — actual cells, actual formulas, actual hidden columns — rather than a clean equation on paper. Spreadsheets breed unit errors structurally: intermediate values sit in unlabeled cells, a "$" column silently holds thousands, a formula references a cell three tabs over that changed units, and none of it is visible in the number itself. The audit recovers the quantity structure the spreadsheet failed to record — naming every column and hidden cell, tracing formula chains, and checking that what gets multiplied and summed is actually compatible — and leaves behind a documented trail of what was verified.

Example¶

A construction firm's bid model is producing a total that feels low. The audit walks it. Column C is labelled "Cost" but a spot check shows its values are in thousands of dollars, while column F, also "Cost," is in raw dollars — and a summary cell adds them directly, treating 250 (meaning $250,000) as if it were $250. The number looked fine; the units did not. The audit also finds a "rate" column that is actually a one-time total copied down, and a hidden helper column dividing by crew size that no visible label mentions.

The edge check catches one more: a productivity formula divides by "days on site," and for a fast-track scope that value is zero in one row, silently returning a division error swallowed by an IFERROR that substitutes a plausible-looking zero. The audit's output is a marked-up map of the model — every column and hidden cell named with its true unit and denominator, the incompatible sums flagged, the zero-denominator row surfaced — recorded so the same model can be re-checked after edits rather than re-discovered from scratch.

How it works¶

Inventory every cell that carries meaning. Name each column, summary cell, and hidden intermediate with its true unit, denominator, and scale — recovering the variable list the spreadsheet never wrote down.
Trace the formula chains. Follow references across cells and tabs to see what is actually being combined, since the displayed number hides which units met in the operation.
Test the combinations. Check that summed and multiplied cells are dimensionally compatible — no thousands added to units, no rate summed with a total.
Probe the fragile cells. Push denominators and lookups to their edges (empty, zero, missing) to find divisions and references that fail silently behind error-suppression.

Tuning parameters¶

Audit depth — headline columns only, or every hidden helper cell and cross-tab reference. Deeper audits catch the sneakiest errors at real time cost.
Formula-tracing breadth — how far across tabs and workbooks you follow references. Broader tracing catches upstream unit changes; narrower stays local and fast.
Labeling standard imposed — how strictly you require units in cell labels going forward; stricter labeling prevents recurrence but adds upkeep.
Error-suppression scrutiny — how hard you look behind IFERROR/IFNA wrappers that can mask exactly the edge failures worth finding.

When it helps, and when it misleads¶

Its strength is meeting the error where it lives. Spreadsheets are where most real-world quantitative work actually happens, and their unlabeled intermediate cells are a notorious source of unit and denominator mistakes; a systematic cell-by-cell walk is often the only thing that surfaces them.

Its limits are coverage and staleness. A large model has more cells than any audit fully traverses, so sampling can miss the one bad cell, and an audit is a snapshot — the next edit can reintroduce the very error just fixed, especially without an enforced labeling convention. It also verifies structure, not truth: a dimensionally clean model can still use the wrong formula. The discipline is to prioritize the cells feeding the headline outputs, impose unit labels so fixes stick, and re-audit after significant edits rather than trusting a one-time clean bill.

How it implements the components¶

variable_list — it reconstructs the list of every quantity, denominator, and hidden intermediate the spreadsheet failed to name.
audit_trail — its marked-up map of the model, recording what was checked and found, is the retained trail for re-checking later.
consistency_test — testing that summed and multiplied cells are dimensionally compatible is the core verification.
edge_case_quantity_check — probing zero denominators and missing lookups behind error-suppression is a first-class step, not a footnote.

It does not fix a flagged mismatch by converting units (that is Unit Conversion Workflow), nor pre-specify the units a model's fields must hold so errors are prevented rather than found (that is Model Input / Output Contract); it detects and records what an existing spreadsheet actually does.

Instantiates: Dimensional Consistency Check — the archetype applied to the live spreadsheet, the artifact where quantity structure most often goes unrecorded.
Consumes: Unit Conversion Workflow is what resolves the mismatches this audit surfaces.
Sibling mechanisms: Model Input / Output Contract · Unit Check · Cross-Scale Transfer Review · Dimensional Analysis Table · Dimensionless Ratio Construction · Engineering Equation Sanity Check · Finance / Accounting Unit Check · Normalized Metric Design · Per-Capita or Per-Unit Conversion · Stock / Flow Separation Check · Unit Conversion Workflow

Notes¶

The audit finds errors after the fact; a Model Input / Output Contract prevents them going forward. On a model that will be reused, an audit is best followed by imposing labels and ranges so the same slip cannot silently return — otherwise each edit reopens the same holes.