Excel Formula won't divide by 0 (zero)

[daginge]

Hi All
I'm trying to work out percentage differences in an Excel Spreadsheet and having trouble going up from 0.
To explain if the figures go down from e.g. 5 to 9 to 0 the percentage is calculated as down by 80% then down by 100% spot on! HOWEVER, if the figures go down from 1 to 0 then up tp 10......no dice! I get a 100% reduction in the first cell and then an #DIV/0! error in the last cell. I presume this is because I am trying to divide by 0. Excel error help says change the figure? Duuuur if i could juggle my figures that well I would be a rich man!
TIA for any assistance.

[squarebear]

If the figure goes from 0 to 10 this is an infinite % increase. What figure was you expecting Excel to come up with?

[Davethedog]

Dagine I'm not sure I get it, but you divide by 1 not 0 you cannot divide something by nothing.

[daginge]

Hi SB what I wanted was to show it as a tenfold increase as there were ten more than previously, am I missing something really basic here LOL

[factor-fiction]

Yes you are missing something. An increase from 1 to 10 would be a ten fold increase. An increase from 0 to 10 is an infinite percentage increase.
That's how percentages work.
Excel is correct
By the way, when you gave an example of figures going down from 5 to 9 and said this was down by 0%, I think you meant to say increased from 5 to 9- which would be an 80% increase

[squarebear]

Indeed. There isn't ten more than previously. Ten times zero is still zero.

[dundurn]

You have two possibilities, I guess.

1 review what your table was trying to show - is a table the best way to display the change over time? - would a graph of the actual figures be a better way to demonstrate what has been going on?

2 use the ISERROR function in Excel to trap the attempt to divide by zero which would allow you to put in a text explanation of the problem

[daginge]

Thanks All, I was told about and given a formula that uses an IF command. It seems to work so we'll go with that for now!