| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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???? |
|
#2
|
|||
|
|||
|
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???? |
|
#3
|
|||
|
|||
|
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???? |
|
#4
|
|||
|
|||
|
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???? |
|
#5
|
|||
|
|||
|
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???? |
|
#6
|
|||
|
|||
|
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???? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel should have a simpler subtraction formula similar to "sum". | Excel Worksheet Functions | |||
| How do I get the formula bar in excel 2003 | Excel Discussion (Misc queries) | |||
| How do I prevent Excel from plotting a cell w/ a formula as a "ze. | Charts and Charting in Excel | |||
| Need excel formula to display 28.50hrs in HRS & MINS? | Excel Worksheet Functions | |||
| Suddenly Excel can't calculate formula!!! | Excel Worksheet Functions | |||
Hybrid Mode
