#DIV/0! - How do i avoid seeing this message?

Hello,

I was wondering is someone can help me please.

I have the attached spread sheet and I use it to monitor if I discount products do I see the uplift in sales.

For Example:

Product 1, I launched it on 21/12/18 and never sold any, discounted it on 26/04/18 and sold 2 in the last 2 weeks therefore averaging 1 per week.

If I average 1 per week for the remaining 31 weeks of the year I should see a big percentage uplift, but because my original sales are zero I see the #DIVO/0! message, is there anyway to trick excel so I see my percentage?

I hope this makes sense?

This states it pretty well: "There is no rate of growth from 0 to any other number. That is to say, there is no percentage of increase from zero to greater than zero and there is no percentage of decrease from zero to less than zero (a negative number). What you have to decide is what to put as an output when this situation happens."

I believe that most consider this as an infinite rate of growth.

To me if sales go from zero to one its a 100% increase so that is what I would like to see, not #DIV/0!.

I've tried the below formulas and non see to do this?

Jeff, I suppose that I should have been more careful with the use of the word "most".

From my experience, I have seen an increase from 0 considered as an undefined increase or an infinite increase.

Neither of which, as you said, mean very much.

Hi Special K,

Thank you for your help.

your formula works if the increase is from zero to 1 it states - 100%.

However if the increase is from zero to 31 it states 100%, where I would like it to say 3100%.

Thank you.

It doesn't make sense but this formula should return what you are looking for:

=IF(D2=0,I2,I2/D2-1)

Hello 63falcondude,

This is perfect . . . . Thank you very much for your help!

Regards,

You're welcome. Thanks for the rep!