+ Reply to Thread
Results 1 to 4 of 4

Variance or Percentage Change?

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    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%

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    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.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    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

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula for Convert Variance to a Percentage Using SUMPRODUCT
    By HangMan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-01-2015, 03:06 PM
  2. Convert Variance to a Percentage Using SUMPRODUCT
    By HangMan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-01-2015, 02:33 PM
  3. Percentage Variance Graphs
    By Bobo6782 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-04-2014, 07:00 AM
  4. Replies: 1
    Last Post: 04-16-2013, 03:58 PM
  5. Conditional Formatting using Percentage Variance
    By Skarnald in forum Excel General
    Replies: 3
    Last Post: 02-09-2012, 11:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1