Many carbon calculators are spreadsheets built by copy and paste: one block per species, one row per year, with the same formula repeated and its addresses stepping by a fixed amount. Reading such a sheet by eye is slow and unreliable. A better approach is to audit its pattern and count the outliers.
1. Recover the block geometry first
Print where each block starts and where each labelled row sits relative to the start. In the worked example, a 13-species calculator had a 59-row pitch. A harvest cycle sat at start plus 1, wood density at plus 17, the carbon row at plus 42. Every later check is an offset from the start.
2. Test each block's self-consistency
| Check | Question |
|---|---|
| Own parameters | Does each block cite its own share and parameter cells, not block one's? |
| Moving references | Do the plus-N references move with the block start row? |
| Own equation | Does each block's allometric equation match its own species row? |
| Same series | Does each block read the same diameter-series index as its species row upstream? |
Report a count and the expected reference. "0 of 13 blocks use non-own absolutes" is evidence. "Looks right" is not.
Count relative references, not absolute ones. A tiled sheet uses absolute references that legitimately differ from block to block, because block k owns its own cells. Do not infer a defect from an absolute being reused. Test that offsets move with the start row. A hard-coded list of expected references produces a wall of false mismatches.
3. Diff against a sibling workbook
If a known-good version exists, such as an earlier scenario or a previous run, compare it cell by cell over the full used range, on both the formula strings and the cached values. Formula differences are real edits, often intentional. Cached-value differences are result coverage. A sheet whose formulas equal the twin's but whose cached values are absent was not miscomputed. It was never recalculated and saved.
Consistency is not correctness. An identical twin can still be wrong end to end. In the example, 485 of 485 formulas matched and the carbon was still 22 percent high. The fix is an arithmetic test: evaluate the chain exactly as written and compare with the raw output of the run. Here that was 608,203 against 498,192, a ratio of 1.2208, and it located the defect.
4. Empty headline cells are not necessarily wrong ones
A workbook written by a tool that does not recalculate holds formulas but no cached values. Count the formula cells, and count those with a cached value. If cached values are far fewer and the calculation setting does not force recalculation on load, the chain is intact and has never been evaluated. That is a file-level defect: set recalculation on load. It is not a formula defect, and you should not repair formulas to make numbers appear.
5. A clean pattern with a wrong headline
When the wiring is clean and the total is still wrong, the defect is upstream. Evaluate the chain species by species against the reference on the same basis. A consistent level error with correct wiring points to an input, often referenced from another sheet.
Search the whole workbook for error values such as reference, value and divide-by-zero errors, and locate them. Summaries fed by a sum over a missing reference are ranges whose source sheet was deleted. Report that as the finding.
6. Decompose a wrong total
For a product cell such as area times share times trees per hectare times carbon fraction times biomass per tree, evaluate each factor against the run. A factor that matches to five digits is cleared. In the example, tree counts matched at 0.99998, so only the per-tree biomass remained.
Three failures, three remedies
| Symptom | Real cause | Remedy |
|---|---|---|
| Headline cells empty | Not recalculated since a non-recalculating save | Force recalculation, touch no formula |
| Number disagrees, reference looks wrong | Formula error | Repair the formula |
| Number disagrees, pattern is clean | Input error, such as density or area on an old basis, or an upstream reference error | Repair the input |
Naming the wrong one wastes the reviewer's time. It can also lead to hundreds of formula edits on a branch that changes the output by zero.
Checklist
- Map the block pitch and offsets. - Count self-consistency per block, with the expected value. - Diff formulas and cached values against a twin. - Test the arithmetic against the run's raw output. - Count formulas against cached values. - Search for error values. - Decompose the wrong total factor by factor.
For the wider reconciliation method, see our article on four-level model audit, and for the contrast between spreadsheet and digital monitoring, our piece on spreadsheet MRV versus dMRV.
Move beyond estimates
Verifiers test sampling design, uncertainty and whether a number traces back to the field. TREEO dMRV captures that evidence in real time, in one auditable chain.



