Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from Jun 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)),"")
share|improve this answer
+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

share|improve this answer

In a module;

Function roll()
    Dim i         As Long
    Dim total     As Double
    Dim groupSize As Long
    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
        roll = total / groupSize
        roll = ""
    End If
End Function

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

share|improve this answer
+1 A nice, neat and tidy function! – Remnant Jun 21 '11 at 14:35

You must log in to answer this question.