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)


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
    – user0
    Commented Dec 24, 2018 at 12:03

1 Answer 1


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


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!
    – fotanus
    Commented Dec 24, 2018 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.