Operating on 'blocks' of data

[Guest]

I need to sum monthly figures into yearly totals for a lot of data. i.e. Total 2002= SUM (Jan2002, Feb2002,..Dec2002

Currently, if year1 data is A1:A12 months, year 2 is A13:A24 months, year 3 is A25:A36 etc and I calculate year1 =SUM(A1:A12) and fill down then year 2 will result in =SUM(A2:A13), year 3 =SUM(A3:14) etc.

How do force excel to count year 2 as A13:A24 without explicitly writing the formula for each year? I have looked into INDIRECT, OFFSET and arrays though nothing has worked

Help will be much appreciate

Jo

 

[Frank Kabel]

I need to sum monthly figures into yearly totals for a lot of data.
i.e. Total 2002= SUM (Jan2002, Feb2002,..Dec2002)

Currently, if year1 data is A1:A12 months, year 2 is A13:A24 months,
year 3 is A25:A36 etc and I calculate year1 =SUM(A1:A12) and fill
down then year 2 will result in =SUM(A2:A13), year 3 =SUM(A3:14)
etc..

How do force excel to count year 2 as A13:A24 without explicitly
writing the formula for each year? I have looked into INDIRECT,
OFFSET and arrays though nothing has worked.

Help will be much appreciated

Joe

Hi Joe
if your first formula for year is entered in row 1 (e.g. B1) enter the
following:
=SUM(OFFSET($A$1,(ROW()-1)*12,0,12)
and copy down

Frank