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.

**Table of Contents**hide

**Download Practice Workbook**

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

**2 Methods to Calculate Percentage Change with Negative Numbers in Excel**

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

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.

**Method 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.

**Step 1:**

**⦿** 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**

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

**Step 2:**

**⦿ **Now, we will drag the** fill handle** to apply the formula to the rest of the cells.

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

**Related Content: Calculate Excel percentage difference between two numbers (using formula)**

**Similar Readings:**

**How to Calculate Variance Percentage in Excel (3 Easy Methods)****Find percentage between two numbers in Excel****How to Calculate Monthly Growth Rate in Excel (2 Methods)****Calculate Margin Percentage in Excel (5 Easy Ways)**

**Method 2: ****Calculate the Percentage Change in Excel by Making the Denominator Absolute**

The above formula will not work when the old value is **negative** but 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** or 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**.

**Step 1:**

**⦿** 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**.

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

**Step 2:**

**⦿ **Now, we will drag the** fill handle** to apply the formula to the rest of the cells.

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

**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**.

We will now see** two runarounds** that although can not solve the problem completely but will be 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.

**Step 1:**

**⦿** 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 “

**”. And if the logical test returns**

*Can Not Be Calculated***FALSE**, then the function will return the

**percentage of change**between the two values (

**).**

*(D5/C5)-1***⦿ **Upon pressing **ENTER**, the formula will return the string “** Can Not Be Calculated **” as the new value (

**D5**) or

**Income (This Year)**is

**negative**.

**Step 2:**

**⦿ **Then we will drag the** fill handle** to apply the formula to the rest of the cells.

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

**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**.

**Step 1:**

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

**⦿ **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.

**Step 2:**

**⦿ **Then we will drag the** fill handle** to apply the formula to the rest of the cells.

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

**Read More:** **Subtract a Percentage in Excel (Easy Way)**

**Quick Notes**

🎯 If you want to know how to calculate the difference between two numbers, click on this link to see the article on this topic.

🎯 Or if you are interested in calculating average percentage change in Excel, click on this link to see the article on this topic.

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

**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 do leave a comment below. Have a great day!!!