← All Kits · Excel Kit

The Variance Percentage Traps: Zero Plans, Negative Plans and Percentage Points

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

The variance percentage is one division and it is wrong more often than any other formula on a month-end sheet. Not because people cannot divide, but because the column contains rows the division was never designed for: a plan of zero, a plan below zero, and a plan that is already a percentage.

What you do: use one formula everywhere, =IF(budget=0,"",(actual-budget)/ABS(budget)), and treat rates as points rather than percentages. The rest of this page is the four traps, in the order they cost you.

The short version. Divide by the plan, take the size of the plan not its sign, and never publish a percentage without the absolute number next to it.

Trap 1: the plan is zero

A new region, a new product, a cost line that was not in last year's budget. Budget 0, actual 4,200, and Excel returns #DIV/0!.

The common fix makes it worse:

=IFERROR((actual-budget)/budget, 0)     <- do not do this

That prints 0.0%, which every reader takes to mean the line landed exactly on plan. You have replaced a visible error with an invisible lie. Return an empty string instead, and let the absolute variance column carry the story:

=IF(budget=0, "", (actual-budget)/ABS(budget))

A blank cell is honest. It says there is no percentage here, which is exactly the situation.

Trap 2: the plan is negative

Loss-making lines, contra revenue, hedging accounts, and any budget written as a negative by convention. Plan -15,000, actual -9,000. The business lost 6,000 less than it said it would, which is good news.

The numerator was +6,000 in both. Only the denominator's sign moved, and it took the meaning of the row with it. ABS around the denominator is the whole fix, and it changes nothing on rows where the plan is already positive, which is why it is safe to apply to the entire column rather than to the rows you happened to notice.

Trap 3: the measure is already a percentage

Gross margin planned at 20 percent, actual 25 percent. There are two true sentences and they are different numbers:

• Margin is up 5 percentage points, which is 25 minus 20.
• Margin is up 25 percent, which is 5 divided by 20.

Neither is wrong. Publishing either one without its unit is. "Margin variance: 25%" in a table whose other rows are money variances will be read as a five-point move by half the room and a twenty-five percent move by the other half.

The rule that survives review: for any measure that is itself a rate, report the difference in points, and put the word points in the column header. Keep percent changes for quantities.

Trap 4: the denominator quietly became the actual

Plan 100,000, actual 80,000. Divide the 20,000 miss by the plan and it is 20 percent. Divide it by the actual and it is 25 percent. Now flip it: plan 80,000, actual 100,000. Against plan that is +25 percent, against actual it is +20 percent.

Dividing by the actual always shrinks an overachievement and inflates a miss, which is why the mistake tends to appear in exactly one direction in decks that have been reviewed a few times. The plan is the reference point. It goes on the bottom.

The formula to standardise on

Variance        =C2-B2
Variance %      =IF(B2=0,"",(C2-B2)/ABS(B2))
Points (rates)  =C2-B2, header reads "pts"
Flag            =IF(AND(ABS(D2)>=10000,ABS(E2)>=0.05),"Review","")

Column B is budget, C is actual, D is variance, E is variance percent. The flag needs both conditions, because either one alone puts a 300 percent overspend on a 40 pound line at the top of the list.

Cheat sheet

How to apply this to your own work

1. Open your variance sheet and press Ctrl+` to show formulas. Look at the denominator of every percentage in the column, not just the first one.
2. Replace the whole column with the IF plus ABS version. It is identical on healthy rows, which makes the change easy to verify.
3. Find every IFERROR(...,0) in the workbook and ask what a zero means in that cell. If zero is a real value elsewhere in the column, the fallback has to be blank.
4. Check the header of every rate row and write pts where it belongs.
5. Re-sort by absolute variance and see whether the top five rows change. If they do, the report has been prioritising small bases.

The one habit to keep

Before a variance column goes out, plant one negative plan and one zero plan in a scratch copy and look at what the formula prints. Two rows and thirty seconds. Every trap on this page shows itself immediately, and none of them ever raise an error on their own.

Is there a zero in your percentage column right now that means on plan, or one that means we could not divide?