I have simple task to calculate % between two numbers:

A B %
1 2 +50%
2 1 -50%
1 1 0%

No problem until I dont get 0 as value in columns. Now I have found solution
to solve if A is 0, or if B is 0, but what to do if both values are 0,
because I got error sign and my table must be "clean".

=IF(B11=0;"-100%";IF(A11=0;"100%";(B11/A11)/B11))

This is what I have on right now but for both values 0 I get -100% instead
of 0.

Thank you in advance.

=IF(B11=0;IF(A11=0;"undefined";-100%);IF(A11=0;100%;(B11-A11)/A11))

I don't know where you get your (B11/A11)/B11 from, but in my book it should
be (B11-A11)/A11
A change from 100 to 200 is a 100% increase. With your formula it would be
a 1% increase.

I've also removed the quotes around your 100% and -100%, as I assume you
want numbers rather than text.

Note also that if you've defined a change from 0 to 2 (or to any other
value) as a 100% change, you haven't distinguished between that and a change
from 1 to 2. And a change from 0 to 3 comes out at half the value of a
change from 1 to 3.

You may wish to rethink?
--
David Biddulph

"Gmaz" wrote in message
...
I have simple task to calculate % between two numbers:

A B %
1 2 +50%
2 1 -50%
1 1 0%

No problem until I dont get 0 as value in columns. Now I have found
solution to solve if A is 0, or if B is 0, but what to do if both values
are 0, because I got error sign and my table must be "clean".

=IF(B11=0;"-100%";IF(A11=0;"100%";(B11/A11)/B11))

This is what I have on right now but for both values 0 I get -100% instead
of 0.

Thank you in advance.

My bad on (B11/A11)/B11, Ive copied wrong formula, and thanks for quotes. I
wasnt aware of that, im still learning. Your formula works perfect and its
alot better from mine:

=(IF(A11=B11;0%;IF(B11<=0;-100%;IF(A11<=0;100%;(A11-B11)/A11))))

Thank you again on pointing on my mistakes.

Dalibor

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=IF(B11=0;IF(A11=0;"undefined";-100%);IF(A11=0;100%;(B11-A11)/A11))

I don't know where you get your (B11/A11)/B11 from, but in my book it
should be (B11-A11)/A11
A change from 100 to 200 is a 100% increase. With your formula it would
be a 1% increase.

I've also removed the quotes around your 100% and -100%, as I assume you
want numbers rather than text.

Note also that if you've defined a change from 0 to 2 (or to any other
value) as a 100% change, you haven't distinguished between that and a
change from 1 to 2. And a change from 0 to 3 comes out at half the value
of a change from 1 to 3.

You may wish to rethink?
--
David Biddulph

"Gmaz" wrote in message
...
I have simple task to calculate % between two numbers:

A B %
1 2 +50%
2 1 -50%
1 1 0%

No problem until I dont get 0 as value in columns. Now I have found
solution to solve if A is 0, or if B is 0, but what to do if both values
are 0, because I got error sign and my table must be "clean".

=IF(B11=0;"-100%";IF(A11=0;"100%";(B11/A11)/B11))

This is what I have on right now but for both values 0 I get -100%
instead of 0.

Thank you in advance.

=IF(AND(B11=0,A11=0),"0%",IF(B11=0,"-100%",IF(A11=0,"100%",(B11/A11)/B11)))

but you would be better not outputting text,

=IF(AND(B11=0,A11=0),0,IF(B11=0,-1,IF(A11=0,1,(B11/A11)/B11)))

and format as a percentage

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gmaz" wrote in message
...
I have simple task to calculate % between two numbers:

A B %
1 2 +50%
2 1 -50%
1 1 0%

No problem until I dont get 0 as value in columns. Now I have found
solution to solve if A is 0, or if B is 0, but what to do if both values
are 0, because I got error sign and my table must be "clean".

=IF(B11=0;"-100%";IF(A11=0;"100%";(B11/A11)/B11))

This is what I have on right now but for both values 0 I get -100% instead
of 0.

Thank you in advance.