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