Excel % Variance Formula........

[Guest]

How do I formulate negative variance formulas? Example: 2005 (-) 500
2004 (-) 400 Variance =(-) 100 Variance % (-)100/(-)400 =25%
I want a formula that gives me (-) 25%.........not positive.......

If formula????

 

[N Harkawat]

How about multiplyingthe result by -1?

 

[Guest]

The problem is next month when the numbers change to positive......
Example.....2005 200.... 2004 (-100) Variance 300 Variance % -100/300
-33% which is correct, but formula times (-1) makes it positive 33%.......

Numbers change monthly.....some positive some negative......

 

[Guest]

Bigdon,
In your example the variance change % is actually
300/-100 = -300%
which when multiplied by -1 gives 300%

So from your 2 examples and by applying the logic of multiplying it by -1 I
get

2005 2004 Difference Difference %

-500 -400 100 100/-400 * -1 = -25%
200 -100 300 300/-100 * -1 = +300%

Which is what you want isn't it?

 

[Guest]

Lets try this.....2005 -2,059 2004 -1,289 Variance -770 Variance %
-770/-1289
59.7% should be (-) 59.7%

Next month.......2005 2,278 2004 -5,005 Variance 7,283 Variance %
7,383/-5005
-145.5% should be 145.5%........

 

[N Harkawat]

That is why I have been mentioning to multiply your result by -1

Therefore your formula will be something like this
=-770/-1289 * (-1) = -59.7%

= 7383/-5005 *(-1) = 145.5%

Notice carefully the * (-1) added after the first part of your formula