Excel 2007 : Copy formula but increase row by more than 1

Hi,

I have a column with 1000 cells of data and need to average the numbers 4 at at time which manually will take ages. Below is my problem:

=average(a1:a5) when copied down always changes to average(a2:a6) and average(a3:a7) etc.

Is there any way to copy it so that the row number increases by 4 each time instead of 1, e.g. =average(a1:a5) when copied would become =average(a6:a10) and average(a11:a15) etc.

Any help much appreciated,
Mark

Try

=AVERAGE(INDEX($A$1:$A$1000,1+((ROW(A1)-1)*4)):INDEX($A$1:$A$1000,4+((ROW(A1)-1)*4)))

where A1:A1000 is your range.

Note: A1:A5 is 5 cells.. not 4.. if you want 5, change all 4's in above formula to 5's