I have a column of monthly data which I want to use to create a new quarterly data column. In order to do this, I take increments of 3 months of data from the monthly data column and use the AVERAGE() formula on them.
I have something like;
AVERAGE(D13:D15) Y1(January – March)
AVERAGE(D16:D18) Y1(April – June)
AVERAGE(D19:D21) Y1(July – September)
AVERAGE(D22:D24) Y1(October – December)
Since the data spans almost an entire century, I want to use the autofill feature to make my life a lot easier. To my dismay, Excel isn't recognizing my iteration of 3 cells at a time and instead giving me results something like this;
AVERAGE(D13:D15) Y1(January – March)
AVERAGE(D16:D18) Y1(April – June)
AVERAGE(D19:D21) Y1(July – September)
AVERAGE(D22:D24) Y1(October – December)
AVERAGE(D17:D19) Y1(May – July)
AVERAGE(D20:D22) Y1(August – October)
AVERAGE(D23:D25) Y1(November, December) – Y2(January)
AVERAGE(D26:D28) Y2(February – April)
Rather than:
AVERAGE(D13:D15) Y1(January – March)
AVERAGE(D16:D18) Y1(April – June)
AVERAGE(D19:D21) Y1(July – September)
AVERAGE(D22:D24) Y1(October – December)
AVERAGE(D25:D27) Y2(January – March)
AVERAGE(D28:D30) Y2(April – June)
AVERAGE(D31:D33) Y2(July – September)
AVERAGE(D34:D36) Y2(October – December)
Is there any workaround to this or am I going to have to manually convert all my monthly data to quarterly data?
Best Answer
Assume that your first quarterly cell (the one that contains
=AVERAGE(D13:D15)
) isQ42
. Replace that formula with=AVERAGE(OFFSET($D$13, 3*(ROW()-42), 0, 3, 1))
, and drag/fill that down. TheOFFSET
function lets you access cells without needing to enter their addresses literally (e.g.,D25
); you can say, in effect, the 12th cell down from cellD13
. This formula says,ROW()
) and subtract 42 (the row number of cellQ42
, where you want the Y1 Q1 average). Obviously, this evaluates to 0 in cellQ42
. When this is dragged down to cellQ43
, it evaluates to 1, etc. InQ46
(where you want the Y2 Q1 average), you get 4.D13
, go down the number of months just computed, and go zero columns to the right. Then take theAVERAGE
of the range that is three rows high and one column wide.