maths formula help - percentage variance

[realsongs]

hi all, Happy new year!

I am suffering from brain fog this morning as I cannot seem to workout a simple maths formula.

I am trying to work out the percentage differences on sales and stock levels betwee this year and last. The issue i am having is with the divide by zero error and my IFERROR use.

store 1 has no uplift on stock and sales so my formula is fine here.

store 2 is also ok. however store 3 has 300 units of stock they didn't have last year, so this should say 100%. if I change the iferror value to 1, then the
stock for store 1 will be incorrect (showing 100% increase instead of 0)

Also because of my formula the stock uplift and sales uplift on store 5 is also incorrect.

I hope that makes sense. would be grateful as always if someone could let me know what the correct formula would be to work out the percentage difference in these cases!

Many thanks!!!!!!!!

 

[6StringJazzer]

store 3 has 300 units of stock they didn't have last year, so this should say 100%.

That's not what 100% means. A 100% increase means it doubled. It is undefined to calculate a percentage increase from a baseline of zero. It just has no meaning. It should be left blank. Otherwise store 3 would show 100% if last year were either 0 or 150, which doesn't make sense.

This is why economics reports compare "same store sales" year over year.

Also instead of testing for any error, you should test specifically for division by 0. Otherwise you will hide other types of errors.

=IF(B4=0,"",D4/B4-1)

 

[realsongs]

Appreciate your help with this thank you.