Calculate Percentage Change with Negative Numbers in Excel

The percentage change for negative numbers in Excel cannot be found, conceptually or empirically. If it’s not feasible, how can we use Excel to calculate % change for negative numbers? Different algorithms can be used, however most of the time they seem to yield incorrect or deceptive results. Here, I’ll outline 5 formulas for calculating percentage changes with negative numbers in Excel.


📂  Download Excel File

Download free workbook from here:


Learn to Calculate Percentage Change for Negative Numbers with 5 Examples

While writing this article, “Percentage Change with Negative Numbers in Excel”, we’ve considered a dataset that contains approximately 6 columns and 11 rows. In this dataset, we added Name, Revenue (Last Year), Revenue (This Year), Change, and % Change to make you better understand. But if you want, you can change the entities and put your own values.

Sample dataset containing revenue comparison

Example

1. Calculating Percentage Change When Old Value Is Positive and New Value Is Negative

If you want to calculate percentage change with negative numbers in Excel then this part is for you. We have shown an easy formula. You can achieve this without difficulty. Just follow the given steps as well.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell F6.
  • Secondly, write the following formula on that cell.

=(D6-C6)/(C6)

  • Now tap Enter.

Using Excel formula to calculate percentage change

  • Thirdly, using the fill handle icon, drag the formula to the entire column.

Using fill handle to drag formula on the column of % change

  • Finally, you will see the desired result you wanted to see.

Result after using fill handle to drag formula in the column of % change

Undoubtedly, this is a very handy technique to calculate percentage change with negative numbers in Excel.

📕 Read More: Calculate Profit and Loss Percentage with Formula in Excel

Example

2. Making Denominator Absolute

When both values are negative or if the previous value is negative but the new one is positive, the aforementioned formula will not execute. Because the equation always returns a negative value—which denotes a negative % change—if the previous value is negative and the new one is positive, the percentage change must be positive. When both integers are negative, the same scenario will occur. In these circumstances, we must make the denominator absolute. You can fabricate a denominator absolute to calculate percentage change with negative numbers in Excel. It is also a simple approach. You just need to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, you need to select cell F6.
  • Secondly, just write the following formula on that cell.

=(D6-C6)/ABS(C6)

🔨 Formula Breakdown

Here,

👉  ABS  function refers to the Absolute denominator of cell C6.

  • Then press Enter.

Using the ABSOLUTE function calculate percentage change

  • Using the fill handle icon, you need to drag the formula to the entire column.
  • Consequently, you will find the result.

Result of using the ABSOLUTE function to calculate percentage change for revenue comparison

Truly, this process is straightforward to achieve.

📕 Read More: Calculate Percentage in Excel Using Absolute Cell Reference

Example

3. Showing No Result for Negative Numbers

There is another alternative approach to calculating percentage change with negative numbers in Excel that we know as showing No Result for negative numbers. Just follow the given steps if you want to achieve this.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, you select cell F6.
  • Secondly, you write the following formula on that cell.

=IF(MIN(C6,D6)<=0,”No Result”,(D6/C6)-1)

🔨 Formula Breakdown

Here,

👉  An analytical test (MIN(C6,D6)<=0) will be run via the IF function.

👉  The function will output the string “No Result” if the logical test yields a TRUE result.

👉  The function will also provide the percentage difference between the two values ((D6/C6)-1)) if the logical test is FALSE.

  • Then tap Enter.

Using the IF and MIN functions to calculate % change

  • Use the fill handle icon to drag the formula to the entire column.
  • Eventually, you will find the result.

Percentage change for revenue comparison showing "No Result"

Indeed, this approach is very handy to work on.

📕 Read More: 3 Ways to Add Percentage to Price in Excel with Formula

Example

4. Showing Positive or Negative When Percentages Change in Excel

We have got a way to show Positive or Negative when percentages change in Excel. Maintain the below procedures to achieve this.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell F6.
  • Secondly, write the following formula on that cell.

=IF(MIN(C6,D6)<=0,IF((D6-C6)>0,”Positive”,”Negative”),(D6/C6)-1)

🔨 Formula Breakdown

Here,

👉  The IF function will first execute a logical ((MIN(C6,D6)<=0) test to see if either the initial value or the latest value contains a negative number. When a negative integer is present (TRUE), the second IF function is executed.

👉  A secondary analytical ((D6-C6)>0) test is conducted in the second IF test to see if the new value exceeds the previous one. The second IF function will output the string “Positive” if the latest value is greater than the initial value (TRUE). Additionally, it will return the string “Negative” if the latest value is less than the previous value (FALSE).

👉  The function will produce the percentage of the difference between the two positive values (D6/C6)-1) if the primary IF function’s reasoning test yields FALSE.

  • Tap Enter.

Using the IF and MIN functions to find positive and negative values in the column of % change

  • Thirdly, use the fill handle icon to drag the formula to the entire column.
  • Eventually, you will see the result as well.

Result containing "Positive" or "Negative" for revenue comparison

Consequently, you will find this method very easy.

📕 Read More: 3 Quick Ways to Add 20 Percent to a Price in Excel

Example

5. Using VBA Macro

You can use VBA Macro to calculate percentage change with negative numbers in Excel. Follow the given steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, go to the worksheet.
  • Secondly, press Alt+F11.
  • Then you will get a VBA window.
  • Thirdly, insert a Module there.

Inserting module for Visual Basic for Applications

  • Afterward, paste the code on that module.

Code

Sub PercentageChangeCalculation()
Dim old1Value As Double
Dim new1Value As Double
Dim Changeofpercentage As Double
old1Value = -50
new1Value = -25
Changeofpercentage = (new1Value - old1Value) / old1Value
Changeofpercentage =Changeofpercentage * 100
MsgBox Changeofpercentage & "%"
End Sub

VBA code to calculate percentage change

  • Finally, click on Run.
  • Eventually, you will see the message box as result.

Pop-up window

Truly, this is a unique technique to work on.

📕 Read More: 5 Easy Methods to Add 10 Percent to a Number in Excel


How to Show a Percentage Increase from 0 in Excel

Excel is unquestionably the most popular calculator. We can almost always conduct the computations we need in our daily lives with Excel. We’ll attempt to learn how to use Excel to estimate percentage increases starting at zero. In Excel, calculating percentage increases is essentially extremely easy. When we determine the percentage increment from zero, things do appear to be a little more complicated. In this article, I’ll outline four different ways to use Excel to determine percentage increases starting at Zero. I have considered a dataset of Bell’s Yearly profit to estimate percentage increases starting at zero in Excel using the SIGN and IF functions. Follow the below steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell D7.
  • Secondly, you have to write the following formula on that cell.

=IF(C6=0,SIGN(C7),C7/C6-1)

🔨 Formula Breakdown

Here,

👉  The analytical test, in this case, is C6=0. In the event that this assertion is TRUE, Excel will now display the SIGN(C7) result. Excel will produce the result by dividing C7/C6-1 to determine the percentage increment if the statement is FALSE.

  • Now press Enter.

Using the IF and SIGN functions to show a percentage change of Bell's Yearly Profit

  • Belatedly, you need to use the fill handle icon to drag the formula to the entire column.
  • Finally, you will find the result as well.

The result of using the IF and SIGN functions to show a percentage change of Bell' yearly profit

Truly, this is an easy way to show a percentage increase from 0 in Excel.


📄 Important Notes

You should be aware of the following things while performing the processes mentioned above:

🖊️  You need to be aware of using Excel functions.

🖊️  While using the ABS function, determine the cell carefully.


📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌  Firstly, we assigned the old value Positive and the new value Negative to calculate percentage change with negative numbers in Excel.

📌  Secondly, we fabricated the denominator Absolute using the ABS function.

📌  Thirdly, we showed No Result for negative numbers.

📌  Fourthly, we tried to show positive or negative when percentages change in Excel.

📌  Afterward, we used VBA Macro.

📌  Finally, we tried to solve how to show a percentage increase from 0 in Excel.


Conclusion

We wish that you would be able to use Excel to calculate percentage change for negative numbers using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If you have any skepticism, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.


Related Articles

(Visited 23 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo