need one formula for % change in TY vs LY revenue

[Gene]

I Need to know the % change of revenue $ from this year(TY) vs last year(LY)
with one formula.

I am using TY – LY / LY = % change but result is not always correct as you
can see below.

TY LY % chg
$500 $250 100.0%
-$500 -$250 100.0%
$500 -$250 -300.0%
-$500 $250 -300.0%

I have someone copy & paste many revenue numbers for TY & LY in cells. The
% change cell is a locked formula. But the result is not accurate when gains
or losses vary.

Is there one formula to get a correct % change?
as always thanks in advance!!

 

[Jacob Skaria]

TY in A1 and
LY in B1

=(A1-B1)/ABS(B1)

If this post helps click Yes

 

[Ashish Mathur]

Hi,

Try this =IF(AND(D6<0,E6<0),-(D6/E6-1),D6/E6-1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Gary''s Student]

In cell C1 enter:
=(B1-A1)/ABS(A1)
and copy down:

$500.00 $250.00 -50.00%
-$500.00 -$250.00 50.00%
$500.00 -$250.00 -150.00%
-$500.00 $250.00 150.00%

 

[Gene]

thank you very much!! I am always awed by the expertise and willingness to
help on this forum.

thanks again Ashish.

 

[Gene]

HELP again. Using your formula still creates an error under one condition.
If TY (D6 is a positive number and LY(E6) is negative then the result is a
negative rather than a positive.

TY 100 LY -100 result is -200% but should be +200%.

 

[Jacob Skaria]

Use ABS() for division by absolute value.

TY in A1 and LY in B1

=(A1-B1)/ABS(B1)

 

[Ashish Mathur]

Sorry for the error. As suggested by others, please use the ABS() function.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Gene]

Yes Jacob thanks. The ABS rule worked. I mistakenly thought it didn't.
Thanks again.