Formula to return % gain or loss

[SurfexcelIT]

In a table I have placed a formula to return the sales gain or loss % between one year and the other. The syntax is =IFERROR([@2016]/[@2015];"")-100%

Unfortunately the formula returns a blank value when there is a 100% gain, although it works for all other %'s, loss or gain.

Grateful for your help,

[6StringJazzer]

What numbers give you a blank? I set this up and when I used 100 and 50 (which is a 100% gain) I got a result of 100%. The only time you should get a blank is when the figure for 2015 is 0.

(If 2015 is zero, then the gain is not 100%, it's undefined. You can't calculate a percent gain over zero.)

[SurfexcelIT]

Thank you 6StringJazzer

That's exactly it, I get a blank when 2015 is zero and 2016 is greater than 0. I didn't know that it was impossible to calculate a % over zero, I thought that whatever sales figure we had in 2016 could be represented with a percentage, if compared to the result of the previous year....

[SurfexcelIT]

Perhaps I could modify the formula to read the zero as a 0,001 so that it is not a straight zero, thereby getting a percent back?

[6StringJazzer]

If you look at your formula, you will see that when sales in 2015 is zero, you are trying to divide by zero, which gives an error. Your IFERROR function returns a blank in that case. It is an undefined math operation to divide by zero, so it is also an error if you try it in Excel.

If you use a small number like 0,001 and 2016 sales are 10.000 then your percentage is going to be 10.000.000%!

You could change your formula to use 1 instead of "" but that would be mathematically incorrect. Still, it may be what you want.

So to answer this, you have to think about why you are showing a percentage in the first place. I don't know how you are using this information to manage the business. It might be more meaningful to show growth in euros instead of as a percentage.

[SurfexcelIT]

6StringJazzer: thank you for feedback. I shall leave the formula as is for the time being. Very best,