Percentage Variance With Positive And Negative Values

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)

Leave a Comment