% Variance

[Abdul]

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]

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 -----

 

[Joe User]

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 -----

 

[Abdul]

Thanks All,

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

Thanks again

 

[Abdul]

Thanks Joel,

and sorry for my poor mathamatics as well.

What about if we add an IF condition for the case of 1 as well as Joe
user had added for the case of Zero?

Thanks again

 

[David Biddulph]

So if the old value is the minimum value the result is a divide by zero, and
if the new value is the minimum value the result is -100%.
Is that really what you wanted, Joel?