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,327
  • 4
  • 20
  • 29
rohith kotha
  • 21
  • 1
  • 3
    Superuser is not a code writing service. If you could [edit](https://superuser.com/posts/1353205/edit) your question, adding what you have tried so far, we'll be happy to give you a hand with it – cybernetic.nomad Aug 28 '18 at 20:54
  • 3
    Why do you need a macro for that? Enter the Average formula in cell C12, selecting A1-A12. Then select C1 to C12 and drag down the fill handle – teylyn Aug 28 '18 at 21:15
  • You don’t need to use a macro to solve this problem; but if you really want to write VBA for Excel, and you don’t know where to begin, see [How do I add VBA in MS Office?](https://superuser.com/q/801609/150988) – Scott - Слава Україні Aug 28 '18 at 21:30

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
  • 22,498
  • 2
  • 40
  • 54
  • #Teylyn,, for me step 2 `Then select C1 to C12 and drag down the fill handle`produces a blank Cell. – Rajesh Sinha Aug 30 '18 at 09:33
  • @RajeshS how far did you drag the **fill handle** down? If you select C1 to C12 and drag the **fill handle** down, the formula from C12 will surface again in row 24, 38, 48, etc, with the correct references to the 12 cells in the rows immediately above. The cells in row 13 to 23, 25 to 35, 37 to 47, etc, will be blank because no formulas have been copied into these cells with the **fill handle** drag down operation. – teylyn Aug 30 '18 at 11:02
  • @RajeshS I added some clarification to my answer. – teylyn Aug 30 '18 at 11:06
  • Thanks for your edit, and woah that's really neat. I didn't know you could do that. Very clever!! – BruceWayne Aug 30 '18 at 13:42
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,624
  • 2
  • 12
  • 25
  • If you put the formula in C12, then the 'mod()` function is unnecessary, right? See teylyn's answer. – Zonker.in.Geneva Aug 29 '18 at 10:57
  • @Zonker.in.Geneva for that row yes it's unneeded. But if OP wants every 12th row, they can drag this down. There won't be averages in every cell from C12 on down, o ly every 12th row. Teylyn's answer will fill in an average in C12, C13, ..., C24, C25, etc. – BruceWayne Aug 29 '18 at 12:09
  • no, her answer won't do that. If you select 12 cells, with only the last one containing a formula and drag the fill handle down, Excel will continue the pattern, ie., 11 blank cells, 1 cell with a formula, 11 blank cells, etc. – Zonker.in.Geneva Aug 30 '18 at 13:31
  • @Zonker.in.Geneva - Ohhh, now I see what she meant. That's pretty neat! – BruceWayne Aug 30 '18 at 13:43
-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
  • 8,995
  • 6
  • 15
  • 35