Moving Excel AVERAGE formula over multiple columns

Question

I have a series of quarterly median rent prices over several decades and would like to create a separate list of annual average rents (see link). To get the 1990 average in the example I have used the following formula:

=AVERAGE(C4:F4)

When I try to drag the formula across though, the start and end cells only move one column over (i.e. D4:G4), rather than the four columns necessary to average the four median rents for 1991.

Is there a simple way to copy the formula over and have the column numbers move four across to average subsequent years?

Solution

One way is to use AVERAGE along with INDEX function that returns Cell reference rather than Cell Value to achieve this.

See the screenshot below. Your data is in C4 thru N6 as an example and your Average is expected in C9 onward in adjacent columns.

In C9 put the following formula and drag it across.

{=AVERAGE(INDEX($C4:$N4,1,MATCH(C$8,YEAR($C$3:$N$3),0)):INDEX($C4:$N4,1,MATCH(C$8,YEAR($C$3:$N$3),0)+3))}

Put this formula without braces and then from within the formula bar press CTRL+SHIFT+ENTER to make it an array formula. Drag it below and across.

And just in case you need average for the Entire Set (All three rings put together for the year, its

{=AVERAGE(INDEX($C$4:$N$6,1,MATCH(C8,YEAR($C$3:$N$3),0)):INDEX($C$4:$N$6,3,MATCH(C8,YEAR($C$3:$N$3),0)+3))}

Assumptions - Your data is always in blocks of 4 quarters per year. Your Year is in contiguous cells in column C8, D8, E8 and so on.

Comments

[Palaeologus]

I've followed your instructions but am unfortunately getting incorrect averages (see screenshot). The formula I've used in the image was {=AVERAGE(INDEX($B$3:$M$5,1,MATCH(B8,YEAR($B$2:$M$2),0)):IND‌​EX($B$3:$M$5,3,MATCH‌​(B8,YEAR($B$2:$M$2),‌​0)+3))} However, the average is coming up as 177.9 rather than 190 (despite all the quarterly results being 190). Where have I gone wrong?

[patkim]

It looks like you are calculating Average for each Ring. I have taken Average for the entire set. This can be easily corrected! I shall edit the answer accordingly.