How to define the cell to use with a math formula, as a function of current cell?

Question

I want to create a formula which, once copied to the cell bellow, references three other cells from a column. So, for instance, look at this image:

You can see that what I need is to define a dynamic formula, which I can copy to the other cells, that yields this result:

cell S3: =AVG(G3:G5) cell S4: =AVG(G6:G8) cell S5: =AVG(G9:G11) cell SN: =AVG(GN1:GN2)

where

N1 = (N-2)*3 N2 = (N-2)*3+2

Is there a way to achieve this, or reference columns like this some way?

Answer 1

If i got it right, you may try using the following formula on 'S3':

=ROUND(AVERAGE(OFFSET(G$3;3*(R3-2);0;3)))

For the cells bellow it, just copy it.

Note that I'm using the level collumn (R) as a helper one. If you do not to use it, you can try rebuild the formula with an ROW(*CurrentCell*), adapting it.

Hope it helps.