Documentary photograph illustrating QA/QC for Carbon Models
← Back to blog

Auditing a Carbon Spreadsheet by Pattern, Not by Reading 800 Rows

Sustainability•5 November 2026•TREEO Indonesia•3 min read

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

CheckQuestion
Own parametersDoes each block cite its own share and parameter cells, not block one's?
Moving referencesDo the plus-N references move with the block start row?
Own equationDoes each block's allometric equation match its own species row?
Same seriesDoes 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

SymptomReal causeRemedy
Headline cells emptyNot recalculated since a non-recalculating saveForce recalculation, touch no formula
Number disagrees, reference looks wrongFormula errorRepair the formula
Number disagrees, pattern is cleanInput error, such as density or area on an old basis, or an upstream reference errorRepair 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.

Related Articles

Audit a Carbon Spreadsheet by Pattern | TREEO Indonesia