Repost: % 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%)

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, A1<B1, A1>B1
with negative values etc. Taking all the possiblities how
can I have a working formula incorporating all
possibilities??!!!
How many IF(AND, ,) / IF(OR, ,) I have to use?

Thanks for your help.

Abdul

 

[Laura C]

Not sure if I've picked up exactly what you are getting at
but can't you use?

=IF(A1<>0,(B1/A1-1)*100,0)

I'm assuming that if A1 is 0 then C1 is 0 also? This
always negative numbers if you don't want negative numbers
use the ABS term
Laura

 

[Abdul]

i think still it will not take all cases.

if A1=0 and B1 >0 C1=100%
and if A1=0 and B1<0 (??%) minus figure

if i use your formulae :

if A1= -1 and B1= -2 wht would be the result and
if A1= -2 and B1= -1 wht would be the result


I am comparing the revenu from two items in two years

so i have all cases

like A1=0, B1 =0
A1=0, B1 >0
A1=0, B1 <0
A1=<0, B1 >0 ........

i mean all the possible combinations

any help ?

Abdul