I'm wanting to show the difference between two values as a percentage. See the possible variations on a theme below:

Three Questions
  1. Is percentage change the same as variance (my understanding is they are not the same thing in mathematical terms)
  2. 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.
  3. 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%