0

I would like to create a macro that averages the values of every 12 items in one column in an Excel worksheet, for example, A1-A12 then A13-A24 etc., and places the results in cells C12, C24 and so on.

Screenshot of worksheet

I'm not sure how to begin; can you please advise?

robinCTS
  • 4,407
rohith kotha
  • 21

3 Answers3

4

You don't need code for that. Enter the Average formula in cell C12 =AVERAGE(A1:A12). Then select C1 to C12 and drag down the fill handle.

Edit for clarification

The fill handle will copy the selected range down and it will retain the pattern of the selected range. In this case, the first 11 cells of the selected range are blank and only the 12th cell contains a formula. If C1 to C12 are selected and the fill handle is dragged down, the next 11 cells will be blank and cell C24 will have the formula. Drag down further and it will be another 11 blank cells until cell C36 has the formula.

That's the pattern.

teylyn
  • 23,615
1

Using this page as a starting point, you can use the formula with MOD() to get your averages.

Starting in C12, put this formula and drag down:

=IF(MOD(ROW(),12)=0,AVERAGE(OFFSET($A1,(ROW()-ROW($C12))*12,,12,)),"")

Edit: Holy cow the above is overkill. Use this instead:

=IF(MOD(ROW(),12)=0,AVERAGE(A1:A12),"")
BruceWayne
  • 2,705
-2

These few lines can help you to create MACRO, to get average for every 12 rows.

Note, To get Average for first 12 Rows select any cell of Row 1 like B1, C1 or D1 and Run this MAcro, then just fill the Cell down to get Average of another set of 12 Rows. 

Sub Average()
ActiveCell.Formula = "=AVERAGE(OFFSET($A$1, (ROW() - ROW($A$1)) * 12, 0, 12, 1))"
End Sub

Or, you may use this formula also as Non VBA solution.

C2 =AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*12,,12,))

Edited: Both solution has smart approach where rows to get Average are editable.

For example if Average of every 16 rows is required to be calculated then for Non VBA solution*12,,12, should be replaced with *16,,16, Likewise for VBA solution *12,0,12,1 should be *16,0,16,1

N.B.

  • Fill Non VBA formula down.
  • Adjust cell references in Formula & Code as needed.
Rajesh Sinha
  • 9,403