need one formula for % change in TY vs LY revenue
 

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!!
--
Gene

Answer: need one formula for % change in TY vs LY revenue
 

Hi Gene,

Yes, there is a formula that can give you the correct % change in revenue from TY vs LY. The formula you are currently using () is not giving accurate results because it is not taking into account the direction of the change (i.e. whether it is a gain or a loss).

The correct formula to use is ((

Formula:

---

TY-LY 


---

)/

Formula:

---

ABS(LY) 


---

)*100. This formula takes into account the direction of the change and gives you the correct % change in revenue.

Here's how to use the formula step by step:

1. Select the cell where you want to display the % change in revenue.
2. Type the formula: =((
   Formula:

   ---

   TY-LY 


   ---

   )/
   Formula:

   ---

   ABS(LY) 


   ---

   )*100
3. Replace "TY" with the cell reference for the current year's revenue.
4. Replace "LY" with the cell reference for the previous year's revenue.
5. Press Enter.

The result will be the % change in revenue from TY vs LY, taking into account the direction of the change.

need one formula for % change in TY vs LY revenue
 

TY in A1 and
LY in B1

=(A1-B1)/ABS(B1)

If this post helps click Yes
---------------
Jacob Skaria


"Gene" wrote:

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!!
--
Gene

need one formula for % change in TY vs LY revenue
 

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gene" wrote in message
...
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!!
--
Gene

need one formula for % change in TY vs LY revenue
 

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%

--
Gary''s Student - gsnu200853


"Gene" wrote:

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!!
--
Gene

need one formula for % change in TY vs LY revenue
 

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

thanks again Ashish.
--
Gene


"Ashish Mathur" wrote:

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gene" wrote in message
...
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!!
--
Gene

need one formula for % change in TY vs LY revenue
 

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%.
--
Gene


"Ashish Mathur" wrote:

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gene" wrote in message
...
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!!
--
Gene

need one formula for % change in TY vs LY revenue
 

Use ABS() for division by absolute value.

TY in A1 and LY in B1

=(A1-B1)/ABS(B1)

--
If this post helps click Yes
---------------
Jacob Skaria


"Gene" wrote:

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%.
--
Gene


"Ashish Mathur" wrote:

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gene" wrote in message
...
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!!
--
Gene

need one formula for % change in TY vs LY revenue
 

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gene" wrote in message
...
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%.
--
Gene


"Ashish Mathur" wrote:

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Gene" wrote in message
...
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!!
--
Gene

need one formula for % change in TY vs LY revenue
 

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


"Jacob Skaria" wrote:

TY in A1 and
LY in B1

=(A1-B1)/ABS(B1)

If this post helps click Yes
---------------
Jacob Skaria


"Gene" wrote:

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!!
--
Gene