Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to trigger data
I have laid out a table containing 50 data on a monthly for a period of 5
years. I intend to calculate growth rate calculated over the same period in different years. So Row 1 contains: Jan-02, Feb-02, Mar-02, ...Jan-03, Feb-03, ... So the growth rate I intend to calculate would be Jan-03/Jan-02, Jan-04/Jan-03, Feb-03/Feb-02, and so on... Is there a function which could locate and trigger the right data and then calculate? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to trigger data
With your monthly data in row #1, In A2 enter:
=(M1-A1)/A1 format as percent and copy this formula across the second row. -- Gary''s Student - gsnu200909 "renegade" wrote: I have laid out a table containing 50 data on a monthly for a period of 5 years. I intend to calculate growth rate calculated over the same period in different years. So Row 1 contains: Jan-02, Feb-02, Mar-02, ...Jan-03, Feb-03, ... So the growth rate I intend to calculate would be Jan-03/Jan-02, Jan-04/Jan-03, Feb-03/Feb-02, and so on... Is there a function which could locate and trigger the right data and then calculate? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to trigger data
With data starting in A2 enter formula in M3
=IF(M2="","",ROUND(M2/A2-1,2)) HTH Peter "renegade" wrote: I have laid out a table containing 50 data on a monthly for a period of 5 years. I intend to calculate growth rate calculated over the same period in different years. So Row 1 contains: Jan-02, Feb-02, Mar-02, ...Jan-03, Feb-03, ... So the growth rate I intend to calculate would be Jan-03/Jan-02, Jan-04/Jan-03, Feb-03/Feb-02, and so on... Is there a function which could locate and trigger the right data and then calculate? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to trigger data
Thanks both for your replies. The standard formula is fine, but what I
intended to achieve is have some sort of automation, so Excel could recognize when it sees a 2nd Jan (Jan-2006), which would automatically detect the figure for the first Jan (Jan-2005), then divide/work out the growth rate, when it sees the 3rd Jan (Jan-2005) occurring, the figure would automatically divide the figure for 2nd Jan... sorry if I didn't express clearly... so is there a formula which allows me to copy across the whole range? Thanks "Gary''s Student" wrote: With your monthly data in row #1, In A2 enter: =(M1-A1)/A1 format as percent and copy this formula across the second row. -- Gary''s Student - gsnu200909 "renegade" wrote: I have laid out a table containing 50 data on a monthly for a period of 5 years. I intend to calculate growth rate calculated over the same period in different years. So Row 1 contains: Jan-02, Feb-02, Mar-02, ...Jan-03, Feb-03, ... So the growth rate I intend to calculate would be Jan-03/Jan-02, Jan-04/Jan-03, Feb-03/Feb-02, and so on... Is there a function which could locate and trigger the right data and then calculate? Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to trigger data
Hi,
Suppose the dates typed rightwards from C3 and then entry is like 1/1/2002,1/2/2002, 1/3/2002, 1/4/2002 and so on (till S3). In row 4, there are numbers In cell C8, enter =1/1/2003 In cell D8, enter =INDEX($C$3:$S$4,2,MATCH(C8,C3:S3,0))/INDEX($C$3:$S$4,2,MATCH(DATE(YEAR(C8)-1,MONTH(C8),DAY(C8)),C3:S3,0)) This will give you the growth of 1/1/2003 over 1/1/2002 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "renegade" wrote in message ... I have laid out a table containing 50 data on a monthly for a period of 5 years. I intend to calculate growth rate calculated over the same period in different years. So Row 1 contains: Jan-02, Feb-02, Mar-02, ...Jan-03, Feb-03, ... So the growth rate I intend to calculate would be Jan-03/Jan-02, Jan-04/Jan-03, Feb-03/Feb-02, and so on... Is there a function which could locate and trigger the right data and then calculate? Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to trigger data
Thanks; this is very helpful
"Ashish Mathur" wrote: Hi, Suppose the dates typed rightwards from C3 and then entry is like 1/1/2002,1/2/2002, 1/3/2002, 1/4/2002 and so on (till S3). In row 4, there are numbers In cell C8, enter =1/1/2003 In cell D8, enter =INDEX($C$3:$S$4,2,MATCH(C8,C3:S3,0))/INDEX($C$3:$S$4,2,MATCH(DATE(YEAR(C8)-1,MONTH(C8),DAY(C8)),C3:S3,0)) This will give you the growth of 1/1/2003 over 1/1/2002 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "renegade" wrote in message ... I have laid out a table containing 50 data on a monthly for a period of 5 years. I intend to calculate growth rate calculated over the same period in different years. So Row 1 contains: Jan-02, Feb-02, Mar-02, ...Jan-03, Feb-03, ... So the growth rate I intend to calculate would be Jan-03/Jan-02, Jan-04/Jan-03, Feb-03/Feb-02, and so on... Is there a function which could locate and trigger the right data and then calculate? Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to trigger data
Yuu are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "renegade" wrote in message ... Thanks; this is very helpful "Ashish Mathur" wrote: Hi, Suppose the dates typed rightwards from C3 and then entry is like 1/1/2002,1/2/2002, 1/3/2002, 1/4/2002 and so on (till S3). In row 4, there are numbers In cell C8, enter =1/1/2003 In cell D8, enter =INDEX($C$3:$S$4,2,MATCH(C8,C3:S3,0))/INDEX($C$3:$S$4,2,MATCH(DATE(YEAR(C8)-1,MONTH(C8),DAY(C8)),C3:S3,0)) This will give you the growth of 1/1/2003 over 1/1/2002 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "renegade" wrote in message ... I have laid out a table containing 50 data on a monthly for a period of 5 years. I intend to calculate growth rate calculated over the same period in different years. So Row 1 contains: Jan-02, Feb-02, Mar-02, ...Jan-03, Feb-03, ... So the growth rate I intend to calculate would be Jan-03/Jan-02, Jan-04/Jan-03, Feb-03/Feb-02, and so on... Is there a function which could locate and trigger the right data and then calculate? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why won't target cell data changes trigger formula evaluation? | Excel Worksheet Functions | |||
Trigger a Date & Time stamp by entering data in another field... | Excel Worksheet Functions | |||
trigger data validation warning | Excel Discussion (Misc queries) | |||
Trigger code if certian data is present | Excel Discussion (Misc queries) | |||
trigger help | Excel Worksheet Functions |