|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 16th, 2009, 09:51 AM
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
May 16th, 2009, 11:26 AM
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
|
|
#3
May 16th, 2009, 11:30 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need one formula for % change in TY vs LY revenue
Hi,
Try this =IF(AND(D60,E60),-(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
|
|
#4
May 16th, 2009, 11:31 AM
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
|
|
#5
May 16th, 2009, 01:17 PM
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(D60,E60),-(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
|
|
#6
May 16th, 2009, 01:29 PM
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(D60,E60),-(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
May 16th, 2009, 01:49 PM
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(D60,E60),-(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
May 16th, 2009, 02:00 PM
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(D60,E60),-(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
May 16th, 2009, 02:08 PM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
