How to Calculate Percentage Change with Negative Numbers in Excel

Get FREE Advanced Excel Exercises with Solutions!

Theoretically and even practically, you cannot find the percentage change for negative numbers. If not possible, then how can we calculate percentage change with negative numbers in Excel? You can certainly use different formulas, however, they appear to produce inaccurate or misleading results most of the time. Here I will show 2 methods to calculate the percentage change with negative numbers in Excel.


How to Calculate Percentage Change with Negative Numbers in Excel: 2 Methods

The formula for percentage change between any two numbers is like below.

Percentage Change Formula

Let’s assume a scenario where we have an Excel file that contains information about the income or earnings of 5 different companies in 2 consecutive years. We will use the incomes of these companies to calculate percentages with negative numbers in Excel. The image below shows the worksheet that we are going to work with.

Percentage Change with Negative Numbers in Excel


1. Calculate Percentage Change in Excel When Old Value is Positive and New Value is Negative

If the old value is positive while the new one is negative, we can use the below formula to calculate the percentage change.

Steps:

⦿ First, we will write down the below formula in cell F5.

=(D5-C5)/(C5)

Formula Breakdown:

Here,

D5 = Income(This Year) = New Value

C5 = Income (Previous Year) = Old Value

Percentage Change When Old Value is Positive and New Value is Negative

⦿ Upon pressing ENTER, we will get the percentage change between negative Income (Previous Year) and positive Income (Previous Year).

Percentage Change with Negative Numbers in Excel

⦿ Now, we will drag the Fill Handle to apply the formula to the rest of the cells.

Percentage Change When Old Value is Positive and New Value is Negative

⦿ Finally, we will see all the percentage changes between the negative valued Income (Previous Year) and positive valued Income (Previous Year).

Percentage Change with Negative Numbers in Excel

Read More: Percentage Difference Between Two Percentages in Excel


2. Calculate the Percentage Change in Excel by Making the Denominator Absolute

The above formula will not work when the old value is negative and the new one is positive or both of them are negatives. Because if the old value is negative while the new one is positive, then the formula will always produce a negative value that indicates a negative percentage change. In this example, loss for the company while in reality, the company makes a profit and hence the percentage change should be positive. The same situation will arise when both of the numbers are negative. In such cases, we will have to make the denominator absolute.

Steps:

⦿ First, we will write down the below formula in cell F5.

=(D5-C5)/ABS(C5)

Formula Breakdown:

Here,

D5 = Income(This Year) = New Value

C5 = Income (Previous Year) = Old Value

The ABS function in Excel will make the denominator value absolute.

Percentage Change with Negative Numbers in Excel

⦿ Upon pressing ENTER, we will get the percentage change between negative Income (Previous Year) and positive Income (Previous Year).

Calculate the Percentage Change by Making the Denominator Absolute

⦿ Now, we will drag the Fill Handle to apply the formula to the rest of the cells.

Percentage Change with Negative Numbers in Excel

⦿ Finally, we will see all the percentage changes between the negative valued Income (Previous Year) and positive valued Income (Previous Year).

Calculate the Percentage Change by Making the Denominator Absolute

But, there is a catch!!!

Look carefully at the percentage changes in income for companies B and E. Both the percentage changes are positive, but the change in the income of E is much lower than that of B. In reality, E has earned more profit than B.

Percentage Change with Negative Numbers in Excel

Read More: How to Calculate Percentage Increase Between Three Numbers in Excel


We will now see two runarounds that although can not solve the problem completely. But they are able to mitigate it to a great extent.

Alternative Method 1: No Result for the Negative Numbers in Excel

In the first method, we will look for the negative numbers in both the old and new values. If we find a negative value, we will then show a text to tell the viewer that a percentage change is not possible.

Steps:

⦿ First, we will write down the below formula in cell E5.

=IF(MIN(C5,D5)<=0,"Can Not Be Calculated",(D5/C5)-1)
Formula Breakdown:

The IF function will perform a logical test (MIN(C5,D5)<=0). If the logical test returns TRUE, the function will return the string Can Not Be Calculated. And if the logical test returns FALSE, then the function will return the percentage of change between the two values ( (D5/C5)-1).

No Result for the Negative Numbers

⦿ Upon pressing ENTER, the formula will return the string Can Not Be Calculated as the new value (D5) or Income (This Year) is negative.

Percentage Change with Negative Numbers in Excel

⦿ Then we will drag the Fill Handle to apply the formula to the rest of the cells.

No Result for the Negative Numbers

⦿ Finally, we will see the values that the formula will return based on the logical test.

Percentage Change with Negative Numbers in Excel

Read More: How to Calculate Year over Year Percentage Change in Excel


Alternative Method 2: Display Positive or Negative Percentage Changes in Excel

Another way is to show a P or L if there is a negative number and the company makes a profit or incurs a loss.

Steps:

⦿ First, we will write down the below formula in cell F5.

=IF(MIN(C5,D5)<=0,IF((D5-C5)>0,"P","N"),(D5/C5)-1)

Formula Breakdown:

  • First IF function will perform a logical test (MIN(C5,D5)<=0) to determine if there is a negative number in old and new values. If there is a negative number (TRUE), then it will perform the second IF function.
  • The second IF test performs another logical test ((D5-C5)>0) to determine if the new value is greater than the old value. If the new value is greater than the old value (TRUE), then the second IF function will return the string P (Indicates a positive change). And if the new value is smaller than the old value (FALSE), then it will return the string N (Indicates a negative change).
  • If the logical test in the first IF function returns FALSE, then the function will return the percentage of change between the two positive values ((D5/C5)-1).

Display Positive or Negative Changes

⦿ Upon pressing ENTER, the formula will return the string N as the new value (D5) or Income (This Year) is smaller than the old value (C5) or Income (Previous Year). The N indicates that there is a negative change or decline in income.

Percentage Change with Negative Numbers in Excel

⦿ Then we will drag the Fill Handle to apply the formula to the rest of the cells.

Display Positive or Negative Changes

⦿ Finally, we will see the values that the formula will return based on the logical test.

Percentage Change with Negative Numbers in Excel

Read More: Calculate Percentage Difference Between Two Numbers in Excel


Quick Notes

🎯  You can also calculate the difference between two numbers.

🎯  Or if you are interested you can calculate average percentage change in Excel.

🎯 And you can use this Free Template and Calculator to calculate the average percentage in Excel.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


Conclusion

In this article, we have learned how to calculate percentage change with negative numbers in Excel. I hope from now on you can calculate percentage change with negative numbers in Excel very easily. However, if you have any queries or recommendations about this article, please leave a comment below. Have a great day!!!


Related Articles


<< Go Back to Percentage ChangeCalculating Percentages | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo