# Variance or Percentage Change?

1. ## Variance or Percentage Change?

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%  Register To Reply

2. ## Re: Variance or Percentage Change?

1. There are two meanings of variance, and neither of them is what you are doing. The mathematical meaning is from statistics, and gives a measure of how much a set of data varies from its mean. It is calculated as the the sum of the squares of the difference between the data point and the mean of the whole set. The business meaning is the difference between a planned number and the actual number, such as cost or duration. So if you have a budget of \$100,000 and spend \$105,000 your variance is -\$5,000 or -5%. (It's the same calculation you are doing but you are looking at the difference between two successive actuals, rather than planned vs. actual.)

2. Your calculations are correct. When going from zero to some number, the % increase is undefined--it's meaningless. That's why you get a #DIV/0! error. If you increase from zero, you can't say it's a 100% increase. You have to show an error message of some sort, and IFERROR is a good way to capture it.

3. An increase is positive and a decrease is negative, and your original calculation is correct. Don't use ABS or you can't tell from the result whether the number went up or down.  Register To Reply

3. ## Re: Variance or Percentage Change?

Hi 6StringJazzer,

In reality, what I actually need to show is the difference between 'Planned Spend' and 'Actual Spend' in a business context, so that would suggest I should actually say it the other way around, as in ((A-B)/A) *100, rather than ((B-A)/A) *100 because if I planned to spend £10,000 and I actually spent £20,000 that is a negative outcome rather than a positive outcome. Having said that I suppose it depends how that is interpretted because I suppose most business people would see a positive value as an overspend and a negative value as an underspend, so would it be true to say that the 'Variance' should be (A-B)/A)*100 whereas the Percentage Increase/Decrease should be ((B-A)/A) * 100

So Example 1 is showing a -100% Variance but a 100% Increase in Spend?

Example 1
2015 Planned Spend = £10,000
2015 Actual Spend = £20,000

Percentage Change = (2015 Planned Spend - 2015 Actual Spend) / 2015 Actual Spend

= (10,000 - 20,000) / 10,000 = -1 or -100%

Example 2
2015 Planned Spend = £0
2015 Actual Spend = £20,000

Percentage Change = (2015 Actual Spend - 2014 Actual Spend) / 2014 Actual Spend

= (0 - 20,000) / 0 = #DIV!0 or N/A  Register To Reply

4. ## Re: Variance or Percentage Change?

A negative variance means that actual performance was worse than planned, and positive means better than planned.

In example 1, the percentage change from planned to actual is 100% but the variance is -100%.

In example 2, the percentage both numbers are undefined as percentages.  Register To Reply