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

Question

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?

Answer 1

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)),"")

Answer 2

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

Answer 3

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.