Percent calcuation causes divide by zero error

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?
 

Question Info


Last updated September 13, 2019 Views 23,031 Applies to:
Well technically, any percentage increase on 0 is infinity, but try putting 0.0001 in for April instead of 0...

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

April is not the zero may is

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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 %

If this post is helpful or answers the question, please mark it so, thank you.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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).

Tushar Mehta (MVP Excel 2000-2015)
Excel and PowerPoint tutorials and add-ins
www.tushar-mehta.com

9 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

That doesn't give you the percent CHANGE - it gives the percent OF.

Percent CHANGE is (May-April)/April

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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))

30 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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))

12 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

This has helped me overwhelmingly. The company that I work for has introduced a number of new products over the past few years meaning that there were no comparable sales for the previous year and this was a constant issue when producing financial reports. Thankyou!  

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hello

Please help me if there is another variant if 0 (zero) is as a percentage calculation, and is not a value.

Thanks

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.