Hi All,

How can I have a formula to get % variance (+/-) without
generating an error?

for eg: if A1=100 and B1=150 then I shud get in C1 50%
and if A1=150 and B1=100 then I shud get in C1 (33.33%)
and if A1= -150 and B1=100 then I shud get in C1 167%)
and if A1= -150 and B1= -100 then I shud get in C1 33 %)
and if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and if A1= -100 and B1= -150 then I shud get in C1 -50 %)

I am having this with the formula (B1/A1)-1 IN C1

Now my problem is that both A1 and C1 can be zero, either
of them or both of them can be negative, one can be
negative and the other positive, A1 Less Than B1 or A1 More than B1
with
negative values etc. Taking all the possiblities how can
I have a working formula??!!!

Thanks for your help.

Abdul

"Abdul" wrote:
How can I have a formula to get % variance (+/-) without
generating an error?

for eg: if A1=100 and B1=150 then I shud get in C1 50%
and if A1=150 and B1=100 then I shud get in C1 (33.33%)
and if A1= -150 and B1=100 then I shud get in C1 167%)
and if A1= -150 and B1= -100 then I shud get in C1 33 %)
and if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and if A1= -100 and B1= -150 then I shud get in C1 -50 %)

You asking for the percentage difference of B1 over A1. Try:

=(B1-A1) / ABS(A1)


----- original message -----

"Abdul" wrote in message
...
Hi All,

How can I have a formula to get % variance (+/-) without
generating an error?

for eg: if A1=100 and B1=150 then I shud get in C1 50%
and if A1=150 and B1=100 then I shud get in C1 (33.33%)
and if A1= -150 and B1=100 then I shud get in C1 167%)
and if A1= -150 and B1= -100 then I shud get in C1 33 %)
and if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and if A1= -100 and B1= -150 then I shud get in C1 -50 %)

I am having this with the formula (B1/A1)-1 IN C1

Now my problem is that both A1 and C1 can be zero, either
of them or both of them can be negative, one can be
negative and the other positive, A1 Less Than B1 or A1 More than B1
with
negative values etc. Taking all the possiblities how can
I have a working formula??!!!

Thanks for your help.

Abdul

"Joe User" <joeu2004 wrote in message
...
You asking for the percentage difference of B1 over A1. Try:
=(B1-A1) / ABS(A1)

The following returns plus or minus 100% when A1 is zero. That's an
arbitrary choice; you might choose something else. Technically, the
percentage difference cannot be computed when A1 is zero.

=IF(A1=0, SIGN(B1), (B1-A1)/ABS(A1))


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"Abdul" wrote:
How can I have a formula to get % variance (+/-) without
generating an error?

for eg: if A1=100 and B1=150 then I shud get in C1 50%
and if A1=150 and B1=100 then I shud get in C1 (33.33%)
and if A1= -150 and B1=100 then I shud get in C1 167%)
and if A1= -150 and B1= -100 then I shud get in C1 33 %)
and if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and if A1= -100 and B1= -150 then I shud get in C1 -50 %)

You asking for the percentage difference of B1 over A1. Try:

=(B1-A1) / ABS(A1)


----- original message -----

"Abdul" wrote in message
...
Hi All,

How can I have a formula to get % variance (+/-) without
generating an error?

for eg: if A1=100 and B1=150 then I shud get in C1 50%
and if A1=150 and B1=100 then I shud get in C1 (33.33%)
and if A1= -150 and B1=100 then I shud get in C1 167%)
and if A1= -150 and B1= -100 then I shud get in C1 33 %)
and if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and if A1= -100 and B1= -150 then I shud get in C1 -50 %)

I am having this with the formula (B1/A1)-1 IN C1

Now my problem is that both A1 and C1 can be zero, either
of them or both of them can be negative, one can be
negative and the other positive, A1 Less Than B1 or A1 More than B1
with
negative values etc. Taking all the possiblities how can
I have a working formula??!!!

Thanks for your help.

Abdul

Thanks All,

Joe User, Your formula works fine for me.
I think some of them do not understand the question well.

Thanks again




On Nov 8, 9:21*pm, "Joe User" <joeu2004 wrote:
"Joe User" <joeu2004 wrote in message

...

You asking for the percentage difference of B1 over A1. *Try:
=(B1-A1) / ABS(A1)

The following returns plus or minus 100% when A1 is zero. *That's an
arbitrary choice; you might choose something else. *Technically, the
percentage difference cannot be computed when A1 is zero.

=IF(A1=0, SIGN(B1), (B1-A1)/ABS(A1))

----- original message -----

"Joe User" <joeu2004 wrote in message

...

"Abdul" wrote:
How can I have a formula to get % variance (+/-) without
generating an error?

for eg: if A1=100 and B1=150 then I shud get in C1 50%
and * * if A1=150 and B1=100 then I shud get in C1 (33.33%)
and * * if A1= -150 and B1=100 then I shud get in C1 167%)
and * * if A1= -150 and B1= -100 then I shud get in C1 33 %)
and * * if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and * * if A1= -100 and B1= -150 then I shud get in C1 -50 %)

You asking for the percentage difference of B1 over A1. *Try:

=(B1-A1) / ABS(A1)

----- original message -----

"Abdul" wrote in message
...
Hi All,

How can I have a formula to get % variance (+/-) without
generating an error?

for eg: if A1=100 and B1=150 then I shud get in C1 50%
and * * if A1=150 and B1=100 then I shud get in C1 (33.33%)
and * * if A1= -150 and B1=100 then I shud get in C1 167%)
and * * if A1= -150 and B1= -100 then I shud get in C1 33 %)
and * * if A1= 100 and B1= -150 then I shud get in C1 -250 %)
and * * if A1= -100 and B1= -150 then I shud get in C1 -50 %)

I am having this with the formula (B1/A1)-1 IN C1

Now my problem is that both A1 and C1 can be zero, either
of them or both of them can be negative, one can be
negative and the other positive, A1 Less Than B1 or A1 More than B1
with
negative values etc. Taking all the possiblities how can
I have a working formula??!!!

Thanks for your help.

Abdul