Quickly doing averages over multiple columns and rows.

[Monty_Droppings]

Hello, I have data 3 columns wide and I am trying to take the average of all 3 columns together in groups of 12 rows.

Is there a way I can do this without having to click manually as I have several thousand data points.

 

[WarPigl3t]

Easy

Function myAverage(myRange)
     Application.Volatile
     For Each Cell in myRange
          myTotal = myTotal + myRange
          counter = counter + 1
     Next Cell
myAverage = myTotal / counter
End Function

In whatever cell you want the output to go, type this formula
=myAverage(A1:C12)
I'm assuming of course that your first 12 rows are in column A through C.

 

[WarPigl3t]

And if that doesn't work, try changing the first line to this
Function myAverage(myRange As Excel.Range)

 

[Joyner]

Here's one way with a formula, may be better ways. Assumes data staring in row 1, columns A to C:

First formula:

=AVERAGE(INDEX($A$1:$C$36,1,3):INDEX($A$1:$C$36,12,3))

Second formula and copy down, change the range for your data:

=AVERAGE(INDEX($A$1:$C$36,1+(ROWS($A$1:A1)*12),3):INDEX($A$1:$C$36,ROWS($A$1:A2)*12,3))

 

[Peter_SSs]

Welcome to the MrExcel board!

Here's another way. I've done it in groups of 4 rows, not 12. You should be able to adapt to 12 but if not post back & I will elaborate.

Formula in D2 is copied down.

Excel Workbook
	A	B	C	D
1
2	7	5	6	4.91667
3	3	3	7
4	1	7	8
5	7	1	4
6	8	8	4	5.83333
7	9	8	1
8	9	4	5
9	7	1	6
10	5	3	6	5.75
11	6	3	3
12	8	8	6
13	9	9	3
14	7	9	3	4.83333
15	5	1	9
16	7	1	6
17	1	1	8
18	6	5	6	5.5
19	9	4	3
20
21
Average Rows

 

[Peter_SSs]

.. or if you don't want the averages spread out like that, here's another possibility, similar to Joyner's suggestion.

Excel Workbook
	A	B	C	D
1
2	7	5	6	4.91667
3	3	3	7	5.83333
4	1	7	8	5.75
5	7	1	4	4.83333
6	8	8	4	5.5
7	9	8	1
8	9	4	5
9	7	1	6
10	5	3	6
11	6	3	3
12	8	8	6
13	9	9	3
14	7	9	3
15	5	1	9
16	7	1	6
17	1	1	8
18	6	5	6
19	9	4	3
20
21
Average Rows

 

[Joyner]

Hello Peter, thanks for fixing the code to work in all cells, I was too tired last night and I could not quite get the first part of the index to work on the first row. Also, I realize my use of the entire range is unnecessary, just the first and last column as you have it.

Thanks