Calculating % Change is so easy… to make a mistake!

How to calculate percentage change with negative number?

I believe calculating percentage change should have a very top ranking on the most frequently used calculations in spreadsheet.  Agree?

For me, I often simply do the calculation with a simple formula:  “=After/Before – 1” which is a simplified form of “(After-Before)/Before”.

It sounds alright…… only if you are not dealing with negative number!!

If it happens that the BEFORE is negative, it gives you a negative result regardless of your AFTER performance.  Don’t get it?  Take a look at the below example:


Pls note the use of ABS function in the formula.  The Syntax is


which returns the absolute value of a number. The absolute value of a number is the number without its sign.

In this case, you will get the correct answer even if you have negative number BEFORE

Convenience has a cost; you have to pay the price sometimes


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips, Formula and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.