[BEGINNER] How to make the formula jump cells when expanding?

[Aughar]

Hi,

I want to compare two statistical samples. However one is given in yearly and the second one is given quarterly.
I calculated the average of the first year with =AVERAGE(F5:I5) but when I expand I will calculate =AVERAGE(G5:J5). Is there any way to have it jump to =AVERAGE(J5:M5) when expanding? There are a lot of data and entering the formula manually would be quite inefficient.

Cheers,
August

[RPADC]

Can you upload a workbook example that demonstrates your problem?

[Aughar]

Thanks for the reply.

I have sampled some of the data in the attachment.
As you can see I want to make a new series of the yearly average. The original data contains about 40 years and I simply want to drag the expansion cross after entering one function. I considered using sumproduct and adding LEFT("respective year";4) as a criteria, but I am not sure on how to use it correctly.

[ATTACH]363109[/ATTACH

[Speshul]

Try using this OFFSET function

Put this formula in cell B6, and copy across to C6, D6, and so on.

=AVERAGE(OFFSET($E$2:$H$2,0,(COLUMNS($B$5:B5)*4)-4))

It works by offsetting the array E2:H2 by a number of cells. The number of cells is generated by the COLUMNS() function, which will return a 1 in the first cell, then a 2, then a 3 and so on, as you copy the formula to the right

It it multiplied by 4, because we want to offset by 4 cells each time. so the first cell will actually have OFFSET(E2:H2,1*4), so it will offset the area to the right by by 4 cells. The second one will be OFFSET(E2:H2,2*4), which will offset the area by 8 cells, and so on.

I have a -4 in there because i want the first cells offset to be 0. then 4, then 8, as you copy, since the first cell will have COLUMNS() = 1, then *4 = 4, and i don't want to OFFSET until the formula is copied left, so i take that initial 4 away.

Hope that makes sense.


As a side note, the data would be significantly easier to read if it is possible to transpose it, so the data set is Vertical instead of Horizontal. You can easily do this by selecting all of the data and copying it, select the location you want to paste to, then right click and go to Paste Special, and use "Transpose"

[Aughar]

Thank you for your reply.
It worked perfectly and made sense.

Cheers,
August