|
Excel % Variance Formula........
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???? |
How about multiplyingthe result by -1?
"BigDon" wrote in message ... 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???? |
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...... "N Harkawat" wrote: How about multiplyingthe result by -1? "BigDon" wrote in message ... 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???? |
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? "BigDon" wrote: 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...... "N Harkawat" wrote: How about multiplyingthe result by -1? "BigDon" wrote in message ... 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???? |
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" wrote: 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? "BigDon" wrote: 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...... "N Harkawat" wrote: How about multiplyingthe result by -1? "BigDon" wrote in message ... 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???? |
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 "BigDon" wrote in message ... 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" wrote: 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? "BigDon" wrote: 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...... "N Harkawat" wrote: How about multiplyingthe result by -1? "BigDon" wrote in message ... 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???? |
| All times are GMT +1. The time now is 05:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com