Percent calcuation causes divide by zero error

Hilda wrote:

I am trying to compare sales by month and i am gettting an error message.  Example April Sales 20, May sales 0, i want to see the percentge of change.  what formula can i use to view this?

It would be helpful if you showed us the formula that is producing the #DIV/0 error.

if April sales is in B2 and May sales is in B3, the percentage change (May over April) should be calculated by:

=B3/B2-1

formatted as Percentage.

In that case, you would not get a #DIV/0 error.  I suspect you are calculating B2/B3-1, which is the percentage change of April over May, usually not the way we talk about change.

However, if May is 0 and June is 15 (B4), you might want to compute the percentage change of June over May.  Ostensibly, that would be:

=B4/B3-1

formatted as Percentage.

That will produce a #DIV/0 error.  There is no mathematically right answer in that case.  You need to make an arbitrary decision about how to reflect change from zero.  My choice:  100% with the sign of the direction of change.  In that case, returning to the May-over-April formula, the general formula is:

=IF(B2=0,SIGN(B3),B3/B2-1)

formatted as Percentage.

PS:  That assumes that no month will have negative sales(!).  As strange as that might sound, it can happen (and it has).  If you want a formula that works in that case as well, try:

=IF(B2=0,SIGN(B3),(B3-B2)/ABS(B2))

Hilda--you are genius!  Thank you for this information:)

Hilda wrote:

I am trying to compare sales by month and i am gettting an error message.  Example April Sales 20, May sales 0, i want to see the percentge of change.  what formula can i use to view this?

It would be helpful if you showed us the formula that is producing the #DIV/0 error.

if April sales is in B2 and May sales is in B3, the percentage change (May over April) should be calculated by:

=B3/B2-1

formatted as Percentage.

In that case, you would not get a #DIV/0 error.  I suspect you are calculating B2/B3-1, which is the percentage change of April over May, usually not the way we talk about change.

However, if May is 0 and June is 15 (B4), you might want to compute the percentage change of June over May.  Ostensibly, that would be:

=B4/B3-1

formatted as Percentage.

That will produce a #DIV/0 error.  There is no mathematically right answer in that case.  You need to make an arbitrary decision about how to reflect change from zero.  My choice:  100% with the sign of the direction of change.  In that case, returning to the May-over-April formula, the general formula is:

=IF(B2=0,SIGN(B3),B3/B2-1)

formatted as Percentage.

PS:  That assumes that no month will have negative sales(!).  As strange as that might sound, it can happen (and it has).  If you want a formula that works in that case as well, try:

=IF(B2=0,SIGN(B3),(B3-B2)/ABS(B2))

I am trying to compare sales by month and i am gettting an error message.  Example April Sales 20, May sales 0, i want to see the percentge of change.  what formula can i use to view this?

The way to compute percentage change is (new - old) / old.  So, if Apr sales were 20 and May were 0, then you would get (0-20)/20 = -1, i.e., -100%.

Of course, if the old were zero, then the result is undefined (or infinite depending on your preference).

Deal with this with a formula along the lines of =IF(old=0,NA(),(new-old)/old) or =IF(old=0,"",(new-old)/old).

Then you've got something wrong with your formula. If the sales are DECREASING you shouldn't have a "DIV/0" message at all.

Hi, the % increase in may is -100 since you don't have any sales the formula will be

=(may sales cell/April sales cell)-1

format cell as %