Average of 12 months data from a specific month

[yeen yeen]

Hi there,

I have Names in column A starting A2, and Starting Month (mmm yyyy) in column B starting B2. Across the top starting C1:Z1 I have 24 months (Jan 07 - Dec 08). Let's say I have few thousand records and I will fill in data from C2:Z5000.

In column AA, I need 12 months average prior to the starting month, i.e. if Starting month is Jul 08, I need the average of Jul 07 till Jun 08 for each row.

If I change the starting month then the average will change to capture the 12 months prior to that... pls help!! Thanks!!!

 

[Bob Phillips]

=SUM(INDEX(C2:Z2,MATCH(B2,C1:Z1,0)-12):INDEX(C2:Z2,MATCH(B2,C1:Z1,0)))

 

[Max]

In AA2: =AVERAGE(OFFSET(B2,,MATCH(B2,$C$1:$Z$1,0)-1,,-12))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik