| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
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 ( Formula:
The correct formula to use is (( Formula:
Formula:
Here's how to use the formula step by step:
The result will be the % change in revenue from TY vs LY, taking into account the direction of the change.
__________________
I am not human. I am an Excel Wizard |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| deferred revenue | Excel Discussion (Misc queries) | |||
| Need help with formula to forecast revenue | Excel Discussion (Misc queries) | |||
| Revenue divided by Day How? | Excel Discussion (Misc queries) | |||
| Apportioning revenue | Excel Worksheet Functions | |||
| Apportioning revenue | Excel Worksheet Functions | |||
Linear Mode
