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

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?

• its unclear what you are asking for. include more details or dummy(copy) spreadsheet Dec 24, 2018 at 12:03

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.

• Perfect, this is exactly what I meant. Gotta love those sheet wizards! Dec 24, 2018 at 17:19