% 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

 

[joel]

Sttistically the calculations you are making are meaningless. taking
percentage increase of a negative number isn't relative.the method o
getting a variance is as follows


Step 1 - Get an average of all the numbers
Step 2 - Calculate the difference of each number from the Average
Step 3 - Square the difference calculated in step 2
Step 4 - Calculate Sigma Square (S^2) which is the Sum of the number
calculated in Step 3
Step 5 - Caluclate the Standard Deviation which is the Square root o
S^2.

You now have a Normailzed Curve and you can compare where each numbe
lies on the normalized curve.

See the following artical at Wikipedia

'Standard deviation - Wikipedia, the free encyclopedia
(http://en.wikipedia.org/wiki/Standard_deviation

 

[David Biddulph]

I think, Joel, that you missed a bit at step 4. Don't you want to divide by
N (or by N-1) ?

 

[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

 

[joel]

The results of the formula has no meaning if you plot a difference of
when going from -5 to 5 you will see what I mean

=(B1-A1) / ABS(A1)


Let B1 = 1 and vary A1 from -5 to +5

A1
-5 = 6/5 = 1.2
-4 = 5/4 = 1.25
-3 = 4/3 = 1.34
-2 = 3/2 = 1.5
-1 = 2/1 = 2
0 = 1/0 = infinity
1 = 0/1 = 0
2 = -1/2 = -.5
3 = -2/3 = -.67
4 = -3/4 = -.75
5 = -4/5 = -.

 

[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

 

[joel]

the best way is to find the minimum value. then measure the percentag
change from the min value


New Value - Old Value
___________________ divide by

Old Value - Minimum value



Normally when you have all positive numbers the minimum value is zero.
Because your numbers are both positive and negative you need a Minimu
value. The minimum value can either be you most negaitive number of an
number the is more negative than any of your numbers. You can use -10
for example

 

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

 

[joel]

Yes!

If you were ploting straight positive numbers and measuring growt
using

0, 10, 20, 30, 40, 50

You would always have the problem with the growth when you start a
zero. You can eliminate that part of the problem