# Do not average the percentages in a budget variance report

Native Excel evidence shows 5.848% from averaging department percentages versus the correct 6.434% total. Includes a zero-budget case and downloadable fixture.

Published 2026-11-15T07:30:00Z by Renaissance Superintelligence Corporation (RSI).

Calculate the total variance percentage from total actual and total budget. In our expense fixture, averaging three department percentages gives 5.848%, while ($686,500 − $645,000) / $645,000 gives 6.434%. A department with zero budget needs an explicit undefined-percentage policy.

## The departments do not have equal budgets

The synthetic October report has three departments. Sales is $14,500 over a $179,000 budget; Engineering is $24,000 over $350,000; G&A is $3,000 over $116,000. Their variance percentages are approximately 8.101%, 6.857% and 2.586%.

The simple mean of those percentages is 5.848%. It gives the smallest and largest budget the same influence. The total row instead divides the $41,500 combined variance by the $645,000 combined budget, giving 6.434%. These are arithmetic results from the published fixture, not a claim about any real company.

An agent can produce the wrong total with syntactically valid formulas and no Excel error values. That is why an error scan is only one control. The acceptance test must also express the meaning of the measure: in this report, aggregate variance divided by aggregate budget.

Native report values before the zero-budget mutation

Department | Budget | Actual | Variance percentage

Sales | $179,000 | $193,500 | 8.101%

Engineering | $350,000 | $374,000 | 6.857%

G&A | $116,000 | $119,000 | 2.586%

Total | $645,000 | $686,500 | 6.434%

## Zero budget is not zero variance

We then set the four October Sales budget rows to zero and recalculated in Excel. Sales actual remained $193,500 and its dollar variance became $193,500. The percentage formula returned #N/A intentionally: =IF(B6=0,NA(),D6/ABS(B6)).

The total still had a valid denominator. Budget fell to $466,000, actual stayed $686,500, and variance became $220,500. Dividing those totals produced approximately 47.318%. An undefined department percentage did not make the total denominator zero.

This fixture uses #N/A to represent a percentage it cannot calculate. That is a documented business rule, not an unexpected spreadsheet defect. Your output may instead require a label such as “no budget.” Specify the policy before generating charts or narrative; replacing an undefined percentage with 0% would assert a different result.

## Validate the measure, including its exception

The useful controls here are the source budget sum, actual sum, dollar variance identity and percentage rule. Apply them to each department and the total. Keep the zero-denominator case separate so a broad “zero formula errors” rule does not silently erase the intended exception.

The formula uses an absolute budget in the denominator. Our tested budgets are nonnegative. It does not establish how a business should report negative budgets, credits or mixed-sign allocations. Those require an agreed definition and their own test cases.

Our first reporting script also failed for a controller reason: its PowerShell aggregation did not read properties from the hashtable records as intended. We replaced it with explicit arithmetic across the three observed values and reran. The published result retains that correction so the native workbook result and the controller’s summary are distinguishable.

## Inspect the altered workbook before reusing the formula

Download the [zero-budget workbook](/research/native-office-2026-11-15/zero-budget.xlsx), [native result](/research/native-office-2026-11-15/percentage-rollup.json) and [script](/research/native-office-2026-11-15/native-percentage-rollup.ps1). The script expects expense-original.xlsx from the synthetic [expense fixture](/examples/excel-to-powerpoint/October%20close.xlsx), copies it and changes only the test budget rows.

The output is an intentional exception case, not the original accepted October report. Its #N/A cell is part of the expected evidence. Keep it in your test set alongside a normal positive-budget case.

Run through [Panes desktop execution](/docs#commands), then use the [workbook verification guide](/blog/verify-ai-generated-excel-workbooks) for source-to-report reconciliation. If the result enters a deck, also check [PowerPoint chart data consistency](/blog/powerpoint-chart-data-consistency); correct workbook arithmetic does not by itself validate the chart’s embedded data.

## Evidence

Prepared scripted execution on one retained 4-vCPU, 16-GB Windows research computer, November 15, 2026. Excel 16.0 build 20527. The native script reached its saved result in 3.310 seconds before finally-block cleanup, API time was 4.233 seconds and controller wall time 4.309 seconds. An earlier PowerShell hashtable aggregation error was corrected and the test rerun; no failed output was accepted. One observed run, not an autonomous-agent or fleet benchmark. Per-task operating cost is unmeasured. Public Office copies have author metadata redacted; all other ZIP members are identical to the native outputs. They were reopened read-only in Office without changing their bytes.

## Sources

- [Exact native arithmetic and zero-budget result](https://usepanes.com/research/native-office-2026-11-15/percentage-rollup.json)

- [Full mutation and validation script](https://usepanes.com/research/native-office-2026-11-15/native-percentage-rollup.ps1)

- [Public-file identity and native reopen](https://usepanes.com/research/native-office-2026-11-15/publication-manifest.json)