Calculating Percentage Increases/Decreases with Zero Values

[Geethoms]

Hi.. I hope someone can help!? I'm trying to calculate the increases and/or decreases in sales figures as percentages to compare sales from this year to last year, but in some instances the value of sales in either year is zero 0.

A1 is this year. B1 is last year. C1 is the percentage increase/decrease.

An example:
A1 B1 C1
This Year Last Year % Increase/Decrease
5 8 -37.5%

I've been using the formula: =(A1-B1)/B1

But how do I use a formula when I have a zero value in either year period, e.g:

A1 B1 C1
0 56 formula??
72 0 formula??

Can a formula be used to calculate and present C1 as a percentage value?
If not, what do I do with C1?

Any help would be greatly appreciated! Thank you in advance!
Gee.

 

[jasonb75]

So what result would you expect from your second example?

Technically the percentage increase from 0 to 72 is infinite.

If you said it was a 100 % increase then it would be giving you false information.

 

[Geethoms]

Jasonb75.. thank you. I actually don't know what to expect from either example! I understand what you're getting at, but is it possible to show the % increase or decrease in either example? How do I treat the results in each case?!

 

[jasonb75]

In the first example, your existing formula should return -100%, which is correct.

In the second example, the value is not an increase, but a new value, so you have nothing to compare to in order to return a % difference, so for the first instance, the result should be N/A, then the subsequent period you would compare to the first figure of 72.

This is one of those things that always seems to create lengthy discussions on difference of opinion, ultimately what is considered correct would be the opinion of senior management or the person(s) scrutinising the figures.

 

[Geethoms]

Jason'.. re the second example, I completely understand your response. I was looking at it and was simply going to state that there is no 'baseline' to compare to from last year and yes, it needs a further year's figures for a comparison. You have worded it better and more technically accurate for me, thank you!

Yes using my existing formula in the first example returns the -100% figure, so I assume that is mathematically correct to state that then in a spreadsheet reporting such sales figures?

 

[jasonb75]

Yes, the -100% result is the correct way to display the result in sales figures, or for that matter, in anything where a positive value is decreased to zero.

A slightly less conventional opinion on the increase from 0.

If, for example you had a sales figure that decreased from 56 to 0 because a product was made obsolete, then you had a new figure of 72 for a subsequent replacement product, then it might be considered acceptable to compare the old sale of 56 to the new sale of 72, which would return an increase of around 26% in place of a 100% decrease and an incomparable figure.

As I said though, not a conventional opinion so it might be frowned upon if it gets noticed.

 

[Geethoms]

Jason.. that's an interesting way to view those figures, and indeed it is a noteworthy opinion to highlight in my interpretation of figures for products returning a zero figure in the current year, thank you!

However, and although it is not specified nor absolutely clear from the figures and info I am looking at, it appears that each of the products are different items in the product range and one is not a replacement for the other. Nonetheless, an interesting note, thanks!

 

[jasonb75]

Thanks for the feedback, hopefully it gives you enough to work with.

Going back to your original question of a formula that you could use, it would be as simple as

=IFERROR((a-b)/b,"no previous data")

 

[Geethoms]

Thanks again Jason. And this formula is also most helpful, very much appreciated.