Cannot Increase Cell Reference Amount by Multiple When Using Fill Handle

Hello,

I'm trying to do the following on the attached worksheet:

Have an Averaging formula for cumulative averaging (M-column) that I can copy down using the fill handle. Each new row of M-column will reference all of the same data in E-column, plus 4 more rows.

Basically the first averaging formula may be unique, but each successive copy of the Average will reference all the the data in E-column, plus the next 4 cells.

If you see what I've done so far, I've manually created each average formula like this:

= ((AVERAGE(E2:E5))^2)
= ((AVERAGE(E2:E9))^2)
= ((AVERAGE(E2:E13))^2)
= ((AVERAGE(E2:E17))^2)
= ((AVERAGE(E2:E21))^2)
Etc...

I would like to not have to manually increment each of the 4 additional cells referenced, but am having trouble trying to figure this out with either Offset or Indirect, which I'm not entirely sure are appropriate anyway.

Thanks for your time in advance.

Try this in L2

Please Login or Register  to view this content.

Hope this helps

or
=AVERAGE($E$2:INDEX($E:$E,ROW(A2)*4+1))^2

Go with Martins' non-volatile solution.

Put it like so in L2

Please Login or Register  to view this content.