Article 8 – Calculating Percentage Change between New and Old Value

Many times, I get inspiration to write about articles when I post responses to questions on Microsoft Community. The inspiration to write about this has come from following post – Percentages

You will also have many occasions particularly when you are asked to compute percentage change between two values. This is very much encountered in Finance industry where you have to report percentage growth for many parameters like revenue, cost etc. Economics has demand growth, supply growth and every other industry has their own growth metric. Telecom will have subscriber growth, ARPU growth and so on.

Note – The article is not about growth between percentage values i.e. it is not about growth between 20% and 30% but between absolute values.

The Excel File used in this article can be downloaded from here. Percentage Growth

There may be cases where growth is always positive new value will >= Old Values also like Human Height.
There may be cases where new value can be > or = or < Old Values like revenue, cost, profit etc.
There may be cases where old value can be 0 like revenue, cost, profit etc.

The conventional formula which is supplied is (New Value – Old Value)/Old Value. The can also be written as (New Value / Old Value) – 1.

Let's calculate percentage growth using this formula.

Now, let's review the results –

1. There is a growth from 18 to 24 and result is 33.33%
2. There is a de-growth from 24 to 18 and result is -25%. Minus sign denotes de-growth.
3. There is a growth from -18 to 24. But result is -233.33% and minus sign denotes de-growth. Clearly, this is wrong.
4. There is de-growth from 18 to -24 and result is -233.33%. Minus sign denotes de-growth.
5. There is a de-growth from -18 to -24 and result is 33.33% and plus sign denotes growth. Clearly, this is wrong.
6. There is a growth from -24 to -18 and result is -25% and minus sign denotes de-growth. Clearly, this is wrong.
7. There is a de-growth from 18 to 0 and result is -100% and minus sign denotes de-growth.
8. There is a growth from -18 to 0 and result is -100% and minus sign denotes de-growth. Clearly, this is wrong.
9. Will give Error. Will discuss this case in later part of the article.
10. Will give Error. Will discuss this case in later part of the article.

Hence, clearly classical formula is wrong. But this works very well only if New Value >=Old Value.

Now, Microsoft KB has following article which defines the right formula for this purpose
https://support.microsoft.com/kb/214078

Note – The above article, mistakenly says alternative formula as =(new_value/original_value)-1 which is wrong as showed by me above.

Now coming back to formula which is given by Microsoft KB article –

= (New Value – Old Value)/ABS(Old Value)

Let's calculate percentage growth using this formula –

1. There is a growth from 18 to 24 and result is 33.33%
2. There is a de-growth from 24 to 18 and result is -25%. Minus sign denotes de-growth.
3. There is a growth from -18 to 24 and result is 233.33%.
4. There is de-growth from 18 to -24 and result is -233.33%. Minus sign denotes de-growth.
5. There is a de-growth from -18 to -24 and result is -33.33%. Minus sign denotes de-growth.
6. There is a growth from -24 to -18 and result is 25%.
7. There is a de-growth from 18 to 0 and result is -100% and minus sign denotes de-growth.
8. There is a growth from -18 to 0 and result is 100%.
9. Will give Error.
10. Will give Error.

So, obviously, (New Value – Old Value)/ABS(Old Value) formula is a correct one and that is what should be used.

Now, Special Case when Old Value is 0. This adds a twist to entire tale as mathematics related to infinity comes into picture. Mathematics related to infinity is not easy and our ancestors grappled with this for thousand of years. Computers are also struggling with this.

Case 1 – When New value is non Zero – There is infinite growth here.
Case 2 – When New value is Zero – There is zero growth here as value doesn't change. But formula would give result of infinite.

Now, there is no single opinion about above 2 cases. It has to be agreed with the concerned department / users about how to handle these cases. Few suggested options for meaningful discussion –

Option 1 – When old value is 0, show the result of NA irrespective whether New Value is Zero or non Zero. In this case, formula would be

=IFERROR((New Value – Old Value)/ABS(Old Value),"NA")

Option 2 – When old value is 0 and  new value is non zero, treat it as 100% growth. In this case, formula would be

=IFERROR((New Value – Old Value)/ABS(Old Value),1)

Option 3 – When old value is 0 and new value is also 0, treat it as 0% growth. In this case, formula would be

=IFERROR((New Value – Old Value)/ABS(Old Value),0)

Now, we can tie up both Option 2 and 3 and can come up with a single formula (B16 = Old Value, C16 = New Value)

=IF(B16=0,IF(C16=0,0,1),(C16-B16)/ABS(B16))

We can finally insert this new formula and recalculate everything again –

Remember Key thing to handle old value as 0 is to reach an agreement with required stakeholders.