0

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:

example

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 – user0 Dec 24 '18 at 12:03
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.

| improve this answer | |
  • Perfect, this is exactly what I meant. Gotta love those sheet wizards! – fotanus Dec 24 '18 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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