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?
02 Answers
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.
2Use 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)
