How to Calculate Percentage Change with Negative Numbers in Excel

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:

Percentage Change Formula

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.

Percentage Change with Negative Numbers in Excel

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

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

Press ENTER.

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

Percentage Change with Negative Numbers in Excel

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

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

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

Percentage Change with Negative Numbers 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.

Percentage Change with Negative Numbers in Excel

Press ENTER.

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

Calculate the Percentage Change by Making the Denominator Absolute

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

Percentage Change with Negative Numbers in Excel

We have 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!

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.

Percentage Change with Negative 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).

No Result for the Negative Numbers

Press 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

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

No Result for the Negative Numbers

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

Percentage Change with Negative Numbers 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).