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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s