# Calculating the average of variably sized blocks of numbers within a column?

In column `A` I have a set of numbers (over 1,000), and I want to get an average of a block of them at a time, (e.g., 10, so `A1:A10` then `A11:A20` and so on) and write these averages into column `B`, such that `B1` contains the average of the first block, `B2` the second block and so on.

Then, in `C1` I would like to have a value which defines the number of rows each average should consider (e.g., 10 = `A1:A10`,`A11:A20`... and 25 = `A1:A25`,`A26:A50`... and so on).

When I change the value of `C1` I want column `B` to update automatically to average using the new block sizes.

How should I go about doing this?

-

## migrated from stackoverflow.comJun 21 '11 at 22:56

This question came from our site for professional and enthusiast programmers.

This question does not show any research effort. The answers, on the other hand, are quite good. –  Jean-François Corbett Jun 21 '11 at 18:20

You can do it without using VBA. Enter the following formula in B1 and fill down to the last row of Column A:

``````=IFERROR(AVERAGE(INDIRECT("A" & \$C\$1*(ROW()-1)+1 & ":A" & ROW()*\$C\$1)),"")
``````
-
+1 because I prefer a formula to VBA where ever possible, but note that `IFERROR()` is only available in Excel 2007 and newer. –  DMA57361 Jun 22 '11 at 9:06

This code will work, assuming you have value in column A (starting in row 1) and input value is in C1.

``````Sub GetAverages()
Dim avgCount As Integer, numberRng As Range

Set numberRng = Range("A1:A" & Range("A1").End(xlDown).Row)
avgCount = Range("C1").Value

For cl = avgCount To (numberRng.Rows.Count - (numberRng.Rows.Count Mod avgCount)) Step avgCount
Cells(cl, 2) = WorksheetFunction.Average(Range("A" & cl - (avgCount - 1) & ":A" & cl))
Next cl
End Sub
``````

Note this will only compute an average where there are enough values e.g. if you enter 5 in C1 and you have only have values for A1:A13 then you only get averages for A1:A5 and A6:A10

Hope this helps

-

In a module;

``````Function roll()
Dim i         As Long
Dim total     As Double
Dim groupSize As Long
Application.Volatile
groupSize = Range("C1").Value
If 0 = Application.Caller.Row Mod groupSize Then
For i = 0 To groupSize - 1
total = total + Application.Caller.Offset(-i, -1).Value
Next
roll = total / groupSize
Else
roll = ""
End If
End Function
``````

put `10` in `C1`, and in `B1` put `=roll()` and drag down/double-corner-click.

-
+1 A nice, neat and tidy function! –  Remnant Jun 21 '11 at 14:35