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

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

I would disagree with your disagreement.

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

No offense taken, whatsoever.
I wonder if we scared away the original poster??

Reply | Quote

No offense taken, whatsoever.
I wonder if we scared away the original poster??

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

I would disagree with your disagreement.

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote