0

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?

Improve this question
0

2 Answers 2

Reset to default
0

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))}

enter image description here

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.

Improve this answer
2
  • 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)):INDEX($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?
    – Palaeologus
    Feb 10, 2017 at 5:58
  • 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.
    – patkim
    Feb 10, 2017 at 6:08
1

Use the following formula:

=AVERAGE(IF(YEAR($C$3:$N$3)=C8,$C$4:$N$4))
press Ctrl+ Shift+Enter at the same time (array formula) for Inner and drag it in the same row
It will check the date (Mar-90,Jun-90...) in C3:N3 to be equal to the year (1990) in C8 and calculate the average of the corresponding Data.

=AVERAGE(IF(YEAR($C$3:$N$3)=C8,$C$5:$N$5)) for Middle (Ctrl+ Shift+Enter at the same time)

=AVERAGE(IF(YEAR($C$3:$N$3)=C8,$C$6:$N$6)) for Outer (Ctrl+ Shift+Enter at the same time)

enter image description here

Improve this answer

Not the answer you're looking for? Browse other questions tagged .