I'm wanting to show the difference between two values as a percentage. See the possible variations on a theme below:
Three Questions
- Is percentage change the same as variance (my understanding is they are not the same thing in mathematical terms)
- The percentage change from any number to zero = 100% (Example 4) but is the percentage change from zero to any number also 100% or is it 0%? (Example 3). If so, how should you best express that with a formula, would you simply use an IFERROR and set the error value to 100% or would you use an IF OR statement that say's IF(OR(A1=0,B1=0),True,False) or does it not really matter.
- Is it valid to express percentage change as a negative value or should it always be expressed as a positive value. Example 4 below shows -100% but should this actually 100%, as in, is the correct formula to use for all the examples actually ABS((2015 Actual Spend - 2014 Actual Spend) / (2014 Actual Spend))?
Example 1
2014 Actual Spend = £10,000
2015 Actual Spend = £20,000
Percentage Change = (2015 Actual Spend - 2014 Actual Spend) / 2014 Actual Spend
= (20,000 - 10,000) / 10,000 = 1 or 100%
Example 2
2014 Actual Spend = £20,000
2015 Actual Spend = £10,000
Percentage Change = (2015 Actual Spend - 2014 Actual Spend) / 2014 Actual Spend
= (10,000 - 20,000) / 20,000 = -0.5 or -50%
Example 3
2014 Actual Spend = £0
2015 Actual Spend = £20,000
Percentage Change = (2015 Actual Spend - 2014 Actual Spend) / 2014 Actual Spend
= (20,000 - 0) / 0 = #Div/0! (because of the division by 0)
Example 4
2014 Actual Spend = £20,000
2015 Actual Spend = £0
Percentage Change = (2015 Actual Spend - 2014 Actual Spend) / 2014 Actual Spend
= (0 - 20,000) / 20,000 = -1 or -100%
Bookmarks