パーセント変化の数式：負の数・ゼロ・空白

A region goes from a $50 loss to a $25 profit, and (C2-B2)/B2 shows -150%. Other rows come back #DIV/0!, or -100% just because the later figure is still blank.

The ordinary formula is fine for two positive numbers. It needs a different denominator, or a label instead of a percent, once the starting value is negative, zero, or missing.

Enter this in A1:C10. Leave B7 and C8 genuinely empty.

In D1 type Change. In D2 enter =(C2-B2)/B2 and fill down to D10. After Percent format, Northeast is 25%. For West, the more useful calculation is =(C4-B4)/ABS(B4), which returns 150% instead of -150%.

Why a loss turning into a profit shows -150%

Filled down and formatted as Percent, the standard formula returns:

West improved by 75, from -50 to 25. Dividing 75 by -50 gives -1.5, which displays as -150%. Southwest’s loss narrowed from -300 to -120, an improvement of 180, but 180 / -300 is -60%. Both results look like a decline because the old value is negative.

Use ABS in the denominator, and when I still wouldn’t report a percent

ABS takes the size of the starting value and leaves the sign of the change alone. =(C4-B4)/ABS(B4) returns 150% for West. The same formula on Southwest returns 60%.

That answers a slightly different question: how large was the move relative to the size of the starting figure? I prefer it when I’m comparing changes that stay on the same side of zero, as Southwest does, and the report says so.

It does not fix every row. On Mountain, -1 to 100, ABS returns 10,100%. The arithmetic is right and still a bad way to describe the result. For a loss that becomes a profit, or a profit that becomes a loss, I’d write n/m (not meaningful) and show the dollar change beside it.

Zero as the starting value returns #DIV/0!

Central starts at 0 and ends at 400, so =(C6-B6)/B6 returns #DIV/0!. There isn’t a finite percentage increase from zero. A label is more honest than an invented rate.

=IF(B6=0,"new",(C6-B6)/ABS(B6)) returns new. Check for zero before you divide. Don’t wrap the formula in IFERROR and replace the error with 0%: =IFERROR((C6-B6)/B6,0) returned 0 here, which would display as 0% and look like nothing changed. IFERROR also hides typo and reference errors. When to catch an error, and when not to, is in IFERROR vs IFNA. The mechanics of #DIV/0! are in how to fix #DIV/0!.

The same B6=0 branch also catches 0 to 0 and 0 to a loss, so new is a reporting choice, not a complete policy for every zero baseline.

Blank ending values show -100%

A blank is treated as zero in this arithmetic. Southeast has a blank October figure, so the standard formula returns #DIV/0!, the same as Central. Pacific has 250 in October and a blank November, so the formula returns -100%. That one is easy to miss in a report: missing data looks like the region lost everything.

For a column that includes all of these cases, I’d use one guarded formula in D2 and fill it to D10:

=IF(OR(B2="",C2=""),"n/m",IF(B2=0,"new",IF(B2*C2<0,"n/m",(C2-B2)/ABS(B2))))

It returns 25%, -25%, n/m, 60%, new, n/m, n/m, n/m, and 0%. B2*C2<0 marks a sign change across zero, which is why West and Mountain become n/m while Southwest, still a loss on both sides, stays 60%. If your policy is to show a percentage for those crossings, drop that test and keep the ABS calculation.

The blank check also catches a formula that returns "". In this test, an old value produced by ="" still caused #DIV/0! in the standard formula, B2="" caught it, and ISBLANK of that cell was FALSE. The difference is in ISBLANK vs empty cells.

Format as Percent; don’t also multiply by 100

Select D2:D10 and apply Percent format. Northeast’s 0.25 then displays as 25%, and later formulas can still use the 0.25. I prefer that to multiplying the formula by 100.

If you multiply by 100 and also apply Percent format, the cell is 25 formatted as a percentage, which displays as 2,500%. The text labels new and n/m stay text in the same column after Percent format is applied.

Percentage points are not the same as percentage change

If a margin goes from 4% to 6%, that is 2 percentage points. The percentage change is (0.06-0.04)/0.04, which is 50%. Both are right; they answer different questions. For margins, rates, or conversion figures, say “percentage points” for the simple difference.

Finding the original value from the percentage

If you have the new value and the percentage change, the original is new / (1 + percentage change). With 1500 in C2 and 25% in D2, =C2/(1+D2) returns 1200.

That only works when D2 is the ordinary numeric percentage, not an n/m or new label. It also returns #DIV/0! when the change is exactly -100%, because the denominator is zero. I got that result on the Pacific row, where the standard formula is -100%.