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

1. ## % 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. ## 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. ## 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. ## 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. ## 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. ## 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.

7. ## 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. ## 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. ## 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.

10. ## 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
•