copying an aggregate average function down multiple rows

maybe something like :
=Aggregate(1,4,offset($A$1,"A"&(Row()-1)*12+1:"A"&Row()*12)

NOT TESTED, just an idea, and you would obviously have to adjust for the Reference column and starting row..

Hope this helps

hi sarah.grady, i don't have the AGGREGATE formula in my computer & have never used it before, so i tested with SUM. it should work the same i guess.
=AGGREGATE(1,4,INDIRECT("A"&1+(ROW(B1)-1)*12&":A"&ROW(B1)*12)))

the 12th cell from A13 should be A24 right?

@dredwolf:
you might be missing the INDIRECT formula

The best I could come up with is using a helper column to give me the cell locations that I needed. You can see my attached sheet.

I entered random numbers from A1:A2000

I then entered 1, 13, 25, 37 in cells C1:C4, selected those cells, then right-clicked and dragged down to row 167 and chose LINEAR FILL. This gives me the starting cell reference for each aggregate function needed for a 2000 row series.

Then, in B1 I entered: =AGGREGATE(1,4,INDIRECT("A"&C1&":A"&C1+11)) and filled that down to row 167.

Seems to work, but does use a helper row. Not sure of how to do it otherwise.

- Moo

* Benishiryo got it without the helper column. Well done!

@benishyro
you probably right, I came up with the thought just before heading to work, so didn't have time to test it out

I would think that by modifying Benishyro's formula it should work

Formula:

Please Login or Register  to view this content.

- Moo

* You can fill that formula down as far as you need to. Once it gets out of range it will return #DIV/0

Not sure why you are using AGGREGATE here, if you are using 4 as the second argument then you aren't ignoring hidden or filtered rows or errors so you could use AVERAGE like this:

=AVERAGE(OFFSET('Sheet 1'!A$1,(ROWS(C$1:C1)-1)*12,0,12))

assumes first formula in C1, change as appropriate