how to trigger data

R

renegade

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

Gary''s Student

With your monthly data in row #1, In A2 enter:

=(M1-A1)/A1 format as percent and copy this formula across the second row.
 
B

Billy Liddel

With data starting in A2 enter formula in M3

=IF(M2="","",ROUND(M2/A2-1,2))

HTH
Peter
 
R

renegade

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
 
A

Ashish Mathur

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
 
R

renegade

Thanks; this is very helpful

Ashish Mathur said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top