Theoretically and even practically, it is not possible to find the percentage change for negative numbers. There are different formulas that can be used, but they appear to produce inaccurate or misleading results much of the time. In this article, we will demonstrate 2 methods to work around these limitations.

The formula for percentage change between any two numbers is as follows:

Suppose we have the dataset below 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.

### Method 1 – 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:**

- Enter the following formula in cell
**F5**:

`=(D5-C5)/(C5)`

**Formula Breakdown:**

**D5** = Income (This Year) =** New Value**

**C5** = Income (Previous Year) = **Old Value**

- Press
**ENTER**.

We have the percentage change between negative *Income (Previous Year)* and positive *Income (Previous Year)*.

- Drag the
**Fill Handle**to apply the formula to the rest of the cells below.

We have all the percentage changes between the negative valued *Income (Previous Year)* and positive valued *Income (Previous Year)*.

**Read More: **Percentage Difference Between Two Percentages in Excel

**Method 2 –**** Making the Denominator Absolute**

The above formula will not work when the old value is negative and the new one is positive or both values are negative. 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. This indicates a 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 have to make the denominator absolute.

**Steps:**

- Enter the below formula in cell
**F5**:

`=(D5-C5)/ABS(C5)`

**Formula Breakdown:**

**D5** = Income (This Year) =** New Value**

**C5** = Income (Previous Year) = **Old Value**

**The ABS function** will make the denominator value absolute.

- Press
**ENTER**.

We have the percentage change between negative* Income (Previous Year)* and positive* Income (Previous Year)*.

- Drag the
**Fill Handle**down to apply the formula to the rest of the cells.

We have all the percentage changes between the negative valued *Income (Previous Year)* and positive valued *Income (Previous Year)*.

**But, there is a catch!**

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

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

We can apply two workarounds that although not able to solve the problem completely, are able to mitigate it to a great extent.

**Alternative Method 1 – ****No Result for the Negative Numbers**

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 show a message to tell the viewer that a percentage change calculation is not possible.

**Steps:**

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

- Press
**ENTER**.

The formula will return the string *Can Not Be Calculated* as the new value (**D5**) or *Income (This Year) *is negative.

- Drag the
**Fill Handle**down to apply the formula to the rest of the cells.

We have the percentage change values that the formula can calculate, and the specified message in the cells that it can’t.

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

**Alternative Method 2 – ****Display Positive or Negative Percentage Changes**

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

**Steps:**

- Enter the below formula in cell
**F5**:

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

**Formula Breakdown:**

- The first
**IF**function will perform a logical test (**MIN(C5,D5)<=0**) to determine if there is a negative number in the old and new values. If there is a negative number (*TRUE*), then it will execute 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*(indicating a*positive change*). And if the new value is smaller than the old value (*FALSE*), then it will return the string*N*(indicating 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**).

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

Drag the** Fill Handle** down to apply the formula to the rest of the cells.

The final results are as follows:

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

**Related Articles**

**<< Go Back to Percentage Change | Calculating Percentages | Calculate in Excel | Learn Excel**