divide by negative looking for percent

[jeffp]

I think I"m missing something here but....I need help for
my job asap (job , what job).
Four cells A1..D1 for comparing last years profit/loss to
this year.
A=2004,B=2003,C=a-b,D=c/b (for a % gain/loss vs. previous
year. It all works fine unless B is negative (a loss
in '03)A or C can be negative but if B is negative it
shows a negative % (as in a loss) and this isn't correct.
Numbers:
$3,665.85 ($1,924.76) $5,590.61 -290%

D should be a Positive increase this year over last.
ANy help would be great.
Thanks,
jeff

 

[Llobid]

Jeff...

I don't think your problem is with Excel, but with your math. If yo
switch things around a little, your problem will be solved. If you ar
trying to find a percent increase or decrease over the previous year
profits (A1 to B1), then your formula in C1 should be:

=B1-A1

Then the formula in D1 should read:

=C1/A1

If for example, you earned $4000 in 2003 and $5000 in 2004, you woul
have a 25% increase ($1000) from 2003 to 2004. If you subtract $400
(A1) FROM $5000 (B1), then divide by $4000 (A1), you will get
positive 25%. If, however the amount in column is lower, the formul
would give you a negative gain...or loss

 

[jeffp]

Maybe I just didn't explain it well enough. I definately
agree w/ you. Subtract last year (b1)from this year (a1)in
column C and it gives you the difference. Now I want to
find what kind of % increase (decrease) this is so in D1 I
divide the $diff(C1) by last years profit(B1). With the
numbers you used it works perfectly. THhis year $5000,last
year $4000, $increase $1000, % increase 25%.A plus or
positive increase.
Now,this same formulas if last year we produced a loss of
$1000. A1 is $5000 B1 is -1000 c1 is $6000. A swell
improvement over last year. BUT, c1/A1 shows a negative
-600%. A minus when we "know" this is a much better
improvement than $5000 this year and $4000 last year.
As I mentioned I'm confused

Jeff

 

[Frank Kabel]

Hi
this is still IMHO a math problem. What would your expected result be?.
Lets assume your last year profit was '0' and you now have a profit of
1000. This is an infinite increase regarding last year's performance.
So what is your expected percentage value if your last year's
performance was negative (infinite*something?)

 

[Jeffp]

Frank,
I'm sure you're right mathematically but I was hoping for
a workaround for a business report. View the following
example. If I hide the first three columns and analyze by
percent change it sure looks like Row 4 was a disaster
when in fact it was a terrrific improvement.
04Sales 03Sales $Change %Change
400 500 -100 -20%
300 500 -200 -40%
1000 -500 1500 -300%

Any more thoughts ? I appreciate learning.
Jeff

 

[Frank Kabel]

Jeff
though I have also studied economics this kind of 'workaround' whish
for a business reports shows a typical 'Dillbert' product wish.
I hope for you Harlan won't read this post and won't reply in his usual
manner <vbg>

But coming back to your example. What kind of result would yOU expect
in this case. As said there's no percentage that can be calculated in
these cases. Problem is no positive percentage could reflect a change
from -500 to a positive amount (just not possible).

What you can do is to show that in these cases you have achieved a
turnaround (just don't calculate anything but show you are back into
the profit region). So something like
=IF(AND(B2<0,A2>0),"Back in profit",IF(AND(B2>0,A2<0),"welcome in the
loss region",C2/B2))

 

[Guest]

Frank,
THanks for your time and example. I came up with something
that worked , even though it's quite choppy. I have a a
few IF's to eliminate the #Div/0! error. I'm just learning
ISERROR! Next time.....
=IF(AND(A1=0, B1=0),"no data either year",IF(A1=0,"no
data '04",IF(B1=0,"no data '03",IF(AND(C1=0,A1=B1),"same
as last year",IF(B1<0,"can't divide by a
negative",C1/B1)))))

THanks again,
Jeff

 

[Llobid]

Jeff...

Frank is absolutely right. In some cases, the percent increase/decreas
doesn't work. For example if your profit the first year is $0...ther
is no possible increase or decrease (you can't get a percent of 0)
However, try putting the following formula in the last cell and see i
this gives you something you can work with.

=IF(A1=0,"N.A.",IF(AND(A1>0,B1>0),C1/A1,ABS(C1/A1))