Average every 15 cells

[apace2]

What I want to do is average every 15 cells. I.e. develop a table which is:

Cell P16 =AVERAGE(B16:B30)
Cell P17 =AVERAGE(B31:B45)
Cell P18 =AVERAGE(B46:B60)
etc
etc
etc

When I try and drag the formula down - I can't get it to work.

I need a way of expanding this formula quickly down the spreadsheet!

Amy help would be appreciated.

Thanks

[63falcondude]

Here's one option using OFFSET:

P16 =AVERAGE(OFFSET(B$16:B$30,15*(ROWS(A$1:A1)-1),0))

[daddylonglegs]

Option 1

=AVERAGE(OFFSET(B$16:B$30,15*(ROWS(P$16:P16)-1),0))

....but because 63falcondude beat me to it.....

Option 2

Put this formula in P16 and copy down

="=AVERAGE(B"&ROWS(P$16:P16)*15+1&":B"&ROWS(P$16:P16)*15+15&")"

That gives you the exact formulas you suggested.....but in text format

Now select the whole range of formulas and do this to convert the "text formulas" to real ones

CTRL+C to copy then Right-click > Paste Special > Values > OK

Then do an "Edit/Replace" and replace = with =

This will give you actual formulas like =AVERAGE(B16:B30) etc.

[tim201110]

=sumproduct($b$16:$b$90*(int(row($b$15:$b$89)/15)=rows($a$16:a16)))/15