Bottom line: Learn how to create percentage change formulas in Excel with negative numbers.
Skill level: Intermediate
In my last post I shared a macro to quickly create percentage change formulas. I also explained two formulas we can use to calculate percentage change.
=(new value – old value) / old value
=(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.
The goal of the percentage change formula is to allow us to make comparisons on performance between two or more items. This could be time periods, entities, categories, businesses, etc. This formula can also be used to calculate discount percentages.
Download the File
Download the file to follow along.
What if the numbers are Negative?
After a bit of research, I have not found a good way to calculate the percentage change when either the old number or the new number are negative. There are definitely formulas you can use, but they seem to produce incorrect or misleading results.
The Wall Street Journal has an old guide to earnings reports that says this:
Net Income percent change is the change from the same period from a year ago. Percent change is not provided if either the latest period or the year-ago period contains a net loss [negative number]. On the digest page, if a company posts a profit in the latest period against a loss in the year-ago period, the percent change is represented as a “P”. Similarly, if a company posts a loss in the latest period against a profit in the year-ago period, the percent change is represented as a “L”.
This article might be outdated, as I have found that recent WSJ financial pages are using the ABS method (more on that below) to calculate percentage change, but the point is that percentage change on negative numbers can produce misleading results.
What about the ABS (Absolute) Method?
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)
Technically this formula works to produce a percentage change number with the correct sign (positive/negative). However, I believe it produces misleading results.
The image below contains an example of this. The Old value is negative and the New value is positive.
- When the value goes from -10 to 50, the amount change is +60 and percentage change is 600%.
- When the value goes from -60 to 50, the amount change is +110 and percentage change is 183.3%
Although the calculation is technically correct because we changed the denominator (old value) to a positive number, the results don't really make sense to me.
If you were just looking at the percentage change numbers, you would think that Coffee did better than Cakes. When in reality Cakes made a huge gain in profit over the prior period, much larger than Coffee.
The same issue happens when the numerator (new value) is negative. Here is another example that produces misleading results.
In this example Cakes had a larger negative value change, but smaller percentage change than Coffee.
But I've Always used the ABS Method!!!
I know, me too. I've seen this method used throughout my career, and it looks like the Wall Street Journal is even using it. I spotted it in some of their growth metrics on this page of Bank of America financials.
It's hard to say that it's completely wrong to use the ABS formula. But, it's good to know that the results might be misleading. So use it with caution! 😉
Alternate Calculations for Percentage Change with Negative Numbers
Here are a few ways to test for the presence of a negative number, and provide an alternate result.
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)<=0,"--",(new value/old value)-1)
Here's how the formula works:
- The logical test of the IF function (MIN(old value, new value)<=0) finds the minimum of the two values and tests if the value is less than or equal to zero. The result will either be TRUE or FALSE.
- If the result is TRUE, then a negative number (or zero) exists. In this case we can display some text to tell the reader. This can be anything you want. I just used two dashes “–“. You could also have it return an N/A error with the NA() function, or any other text that lets the reader know the percentage change could not be calculated.
- If the result is FALSE, then the percentage change formula is used to return the percentage change on the two positive numbers.
This formula also handles the divide by zero (#DIV/0!), so we don't have to wrap it in the IFERROR function.
Method #2: Show Positive or Negative Change
The Wall Street Journal guide says that its earning reports display a “P” or “L” if there is a negative number and the company posted a profit or loss.
We could use this same methodology to tell our readers if the change was positive (P) or negative (N) when either value is negative.
The following formula tests for this with an additional IF function.
=IF(MIN(old value, new value)<=0,IF((new value - old value)>0,"P","N"),(new value/old value)-1)
Here's how the formula works:
- We start with the same logical test to determine if a negative value exists using the MIN function.
- A second IF function is then used to determine if the change from old to new is positive or negative.
IF((new value – old value)>0,”P”,”N”)
This IF statement returns a “P” for a positive change and an “N” for a negative change.
- If both numbers are positive then the percentage change formula is used to display the result.
Other Methods for Percentage Change with Negatives?
Calculating the percentage change when negative numbers are involved is not easy. The ABS method is probably the most commonly used, but it can produce misleading results.
The ultimate goal should be to keep things positive, so we don't have to worry about the negative… 😉
Do you have any other methods for this calculation? Can we further simplify either of the methods above? I'm interested to hear your thoughts. Please leave a comment below with questions or suggestions. Thanks!