Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    % chg from neg to positive not working (Excel 2000)

    I'm trying to show a percent change for sales figures year over year. In '02, we had a negative $3K in sales but in '03 we're up at$12K. When I try to do a percent increase from '02 to '03, I'm receiving a negative number instead of a positive increase. I've also included in my equation something that will allow me to get a "0" where the '02 numbers are "0" so as to not get the DIV/0 error.

    Here is my formula:
    C17= 12,000
    D17= 3,000
    =IF(D17,((C17-D17)/D17),"0")

    Currently, my answer gives me approximately -400% which is not right.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: % chg from neg to positive not working (Excel 2000)

    The percentage change concept is not really appropriate to use with numbers that DO NOT have an absolute zero. You obviously don't have an absolute zero since you went BELOW zero.

    Your D17 value of 3000 (positive) whixh is WHY you get a 400% increase.

    If you place -3000 in D17 you will get the CORRECT value of a -500%.
    Your % change = [12000 - (-3000)] / (-3000) = 15000 / -3000 = -5 = -500%

    Your change is NEGATIVE since you were in the RED. A POSITIVE increase, while you were in the RED indicates that you LOST more money!

    It might be better to say you EARNED 4 times as much PROFIT as you LOST last year instead of using the percent change.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: % chg from neg to positive not working (Excel 2000)

    What do you want the answer to be? From a strict mathematical viewpoint, Excel's answer is correct since a negative percentage change indicates that the change of +15,000 is a different sign from the base number of -3000. If you always want sales increases to be positive and decreases to be negative, you could do something like: =if(d17=0,0,if(c17<d17,-1,1)*abs((c17-d17)/d17)). In your example, this would give you an answer of +500%. The problem with this is that a change from +2000 to +12000 is also a change of +500%. This is not really an Excel problem. I think the problem is that the true mathematical answer is counterintuitive.

    Also, I'm not sure why you have "0" in your if statement. Do you want a numeric zero or a text zero?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: % chg from neg to positive not working (Excel 2000)

    I disagree:
    I think "from a strict mathematical point" the answer is meaningless unless you convert the values to a "ZERO-BASED" system with an absolute zero. (ie no negatives). If there is NOT an absolute zero, the concept of percentage CHANGE is dependent on the arbitrary scale you choose.

    Look (for example) at temperature changes. Is 50

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: % chg from neg to positive not working (Excel 2000)

    I would disagree with your disagreement. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    In the mathematical world, there's no such thing as absolute zero. That only exists in a physical world, which I suppose is where people who report sales numbers operate. In mathematical terms p=(x-y)/y is the percentage change from y to x. If y is negative and x>y, then p is going to be negative even though x>y. It's correct math and I'm glad Excel works that way. I'm a math major who works in a business setting so I've had to explain this concept to people before and it's always tough to explain to non numeric folks.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: % chg from neg to positive not working (Excel 2000)

    OK, using your logic, what if Sales in '02 is zero?

    In the accounting world with correct accrual accounting there shouldn't be such a thing as negative "top-line" sales dollars; it suggests a misclassification by reporting period or by account.

    What the poster probably wants is

    =IF(C17,(D17+IF(C17>0,C17,-C17))/ABS(C17),)

    but I agree with Steve that the result is bogus for any start point of zero or less.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: % chg from neg to positive not working (Excel 2000)

    My real contention is that a percent change in some forms IMPLIES an absolute zero.
    And in the real world there are absolute zeroes. There is absolute temperature scales (Kelvin and Rankine) and you can have an absolute pressure of zero in pure vacuum. You can look at percent changes in these values, but you MUST make sure you are using the absolute scales. Do not use Celsius, do not use Fahrenheit, do NOT use pressures from a pressure gauge (These read atmospheric pressure = 0)

    In the business world, your "implication" is that ZERO is no profit/no loss, but if you start having NEGATIVE to compare you get squirrelly numbers where a -500% change is an INCREASE and GOOD in one scenario and in another that +100% change is a greater LOSS and is BAD. This makes the whole concept of %change to be meaningless to compare numbers.

    Better to look at control charts and determine if your "change" was a real change of random variation, but we digress even further from the question...

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: % chg from neg to positive not working (Excel 2000)

    Quote, "OK, using your logic, what if Sales in '02 is zero?" That's undefined whether you're using my logic or Steve's.
    Quote,"if you start having NEGATIVE to compare you get squirrelly numbers" It's only squirrelly because most people are accustomed to dealing with positive numbers only (i.e. physical measurements where an absolute zero exists). I take back my earlier statement about business financials being in this physical world, because negative numbers happen, the most obvious case being profits. A negative percentage change only means that the sign of the change is different than the sign of the starting number. Because -300=-100*3, -300 is a +200% change from -100. Most people have learned to interpret that mathematical statement as -300 is 200% greater than -100 and that's where the misunderstandings come in.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: % chg from neg to positive not working (Excel 2000)

    Agree that negatives* happen in Financial statements, which is why many of the financial statement ratios are % of something else, rather than % change.

    Anyway, a good discussion here, hope you didn't find our disgreement offensive.

    * accounts which should normally be credit are debit, and vice-versa.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: % chg from neg to positive not working (Excel 2000)

    No offense taken, whatsoever. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    I wonder if we scared away the original poster??

Posting Permissions

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