Excel – Autofill feature isn’t iterating numbers properly when using the AVERAGE() formula in Microsoft Excel

microsoft excel

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)) is Q42.  Replace that formula with =AVERAGE(OFFSET($D$13, 3*(ROW()-42), 0, 3, 1)), and drag/fill that down.  The OFFSET 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 cell D13.  This formula says,

  • Take the current row number (ROW()) and subtract 42 (the row number of cell Q42, where you want the Y1 Q1 average).  Obviously, this evaluates to 0 in cell Q42.  When this is dragged down to cell Q43, it evaluates to 1, etc.  In Q46 (where you want the Y2 Q1 average), you get 4.
  • Multiply by 3.  Obviously, this gives you the number of months since the beginning (January Y1).
  • Starting at D13, go down the number of months just computed, and go zero columns to the right.  Then take the AVERAGE of the range that is three rows high and one column wide.
Related Question