2 minute read
There are 2 formulas to calculate percentage variance (change).
Formula 1
=(new value – old value) / old value
Formula 2
=(new value / old value) – 1
Both of these formulas will produce the same result when the numbers are positive. The one you use is just a matter of personal preference.
What if the numbers are Negative?
One common way to calculate percentage change with negative numbers it to make the denominator in the formula positive. The ABS function is used in Excel to change the sign of the number to positive, or its absolute value.
Here is the formula that is commonly used:
=(new value – old value) / ABS(old value)
This produces misleading results, here the old value is negative and the new value is positive. The greater the value change shows smaller percentage changes.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Old | New | Change | Pct Change | Pct Change Formula |
| 2 | Coffee | -10 | 50 | 60 | 600.0% | =(C2-B2)/ABS(B2) |
| 3 | Tea | -20 | 50 | 70 | 350.0% | =(C3-B3)/ABS(B3) |
| 4 | Cookies | -30 | 50 | 80 | 266.7% | =(C4-B4)/ABS(B4) |
| 5 | Bagels | -40 | 50 | 90 | 225.0% | =(C5-B5)/ABS(B5) |
| 6 | Apples | -50 | 50 | 100 | 200.0% | =(C6-B6)/ABS(B6) |
| 7 | Cakes | -60 | 50 | 110 | 183.3% | =(C7-B7)/ABS(B7) |
Alternate Calculations for Percentage Change with Negative Numbers
Method 1: No Result for Negatives
The first thing we can do is check if either number is negative, and then display some text to tell the reader a percentage change calculation could not be made.
The following formula does this with an IF function and MIN function.
=IF(MIN(old value, new value)