If you copied and pasted (rather than retyped) my formula
=IF(J37-C37=0,0,IF(C37=0,"infinite % difference",(J37-C37)/ABS(C37))) and
Excel reported "The formula you typed contains an error", then I can only
assume that you are working with Windows Regional Options that are looking
for a semi-colon, rather than a comma, as a list separator?
If so, you ought to use
=IF(J37-C37=0;0;IF(C37=0;"infinite % difference";(J37-C37)/ABS(C37)))

If you are still getting a problem, copy and paste the formula from your
formula bar to the newsgroup.

You are right that a percentage of zero is illogical, hence the various
suggestions for reporting the situation, such as my "infinite % difference"
result.
--
David Biddulph

"kaimarja" wrote in message
...
Hi David, Thanks for the reply. Sorry for not being specific enough. Here
are
the answers to your questions:

I use the formula =(J37-C37)/ABS(C37), C being last years value and J
being
this years value.
The value in C37 is 0 and the value in J37 is 70 .
The result cell is formatted to give a % and in all the other result cells
when the formula does not refer to cells containing 0, I get a correct
answer
but not in the above case, where last year's cell contains a 0. The result
shows as #DIV/0!

What I want is that when last year I had 0 and this year I have 70, the
result of my formula should show a % of increase.
I tried to copy and paste the formula you propose in your answer, but I
still get an error message from excel "The formula you typed contains an
error".

Of course I understand I'm asking my formula to calculate a % of 0, which
is
illogical, but I wondered whether there was a way around it.

Thanks for your help.
Kai

"David Biddulph" wrote:

"... it still gives an error" isn't very specific. You haven't told us
what
formula you are using, so we can't tell you what you've done wrong.

If instead of =(E34-D34)/E34 you wanted to start from
=(J37-C37)/ABS(C37),
then instead of
=IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34))
you would end up with
=IF(J37-C37=0,0,IF(C37=0,"infinite % difference",(J37-C37)/ABS(C37)))
If you want the result as a percentage, format the cell with a percentage
format to suit your requirements.
Is that the formula you were using? If so, what input values did you
have
in J37 and C37, what result did you get, and what result did you expect?
--
David Biddulph

"kaimarja" wrote in message
...
Hi David,
I have the same problem as above, but my formula is slightly different:
=(J37-C37)/ABS(C37). I compare two values and want to get the
percentage
of
increase or decrease between the two. Sometimes one of the values (or
both)
is zero, and then it gives an error. I Tried the advice given above but
I
must do something wrong as it still gives an error. Thanks for helping
me.
Kaimarja

"David Biddulph" wrote:

=IF(E34-D34=0,0,IF(E34=0,"infinite % difference",(E34-D34)/E34))

Note that with the formulae which some other contributors have
suggested,
you would get the same answer for an input of D34=99 and E34=0 as you
would
get for D34 and E34 both 99. Is that what you want?
--
David Biddulph

"pgarcia" wrote in message
...
I'm getting #DIV/0! with the following formula, the cell is format
as a
percent. Thanks

=(E34-D34)/E34
E34 = 0
D34 = 0
Result should be 0%