Excel Macro for taking average of each "n" cells

[matiquz]

I have a data sheet with 29 columns (variables) and 5000 rows (meaning 5000 data points for each variables). From this, I want to create a separate data sheet with 29 columns and 500 rows. For each variables I want the average of each 10 rows (row 1 through 10, 11 through 20,....and so on) in the new data sheet. What is the fastest way to do so?

Thanks in advance.

 

[WarPigl3t]

The macro called "getAverage" is the main so run that one. Each macro is a separate task. It helps to stay organized in case you every need to modify the code, its easy to find where to change.

Sub getAverage()
     sht1 = "Sheet1"
     sht2 = "Sheet2"
     firstRow = 2
     Call loopEachColumn(sht1, sht2, firstRow)
End Sub

Sub loopEachColumn(sht1, sht2, firstRow)
     lastColumn = Sheets(sht1).Cells(firstRow, Columns.Count).End(xlToLeft).Column
     c = 1
     Do Until c > lastColumn
          Call loopEachRow(sht1, sht2, firstRow, c)
          c = c + 1
     Loop
End Sub

Sub loopEachRow(sht1, sht2, firstRow, nextColumn)
     r = firstRow
     c = getColumnLetter(nextColumn)
     lastRow = Sheets(sht1).Range(c & Rows.Count).End(xlUp).Row
     Do Until r > lastRow
          Call averageEveryTenRows(sht1, sht2, c, r)
          r = r + 10
     Loop
End Sub

Function getColumnLetter(columnNumber)
     n = columnNumber
     Do
          c = ((n - 1) Mod 26)
          s = Chr(c + 65) & s
          n = (n - c) \ 26
     Loop While n > 0
     getColumnLetter = s
End Function

Sub averageEveryTenRows(sht1, sht2, c, r)
     myCounter = 1
     mySum = 0
     Do Until myCounter > 10
          mySum = mySum + Sheets(sht1).Range(c & r).Value
          r = r + 1
          myCounter = myCounter + 1
     Loop
     myAverage = mySum / 10
     Call printAverageToNextLine(sht2, c, myAverage)
End Sub

Sub printAverageToNextLine(sht2, c, myAverage)
     nextLine = Sheets(sht2).Range(c & Rows.Count).End(xlUp).Row + 1
     Sheets(sht2).Range(c & nextLine).Value = myAverage
End Sub

 

[matiquz]

It's taking average of row 1 through 10, 21 through 30, 41 through 50..... and so on.

Somewhat, it's missing the average of row 11 through 20, 31 through 40....... and so on.

How can I fix this?

Thank you very much.

 

[WarPigl3t]

Look at the sub called "getAverage". Inside you will see where I set all the variables that will be used throughout the entire process. The variable called "firstRow" is set to 2 because I assumed you had headers. Since you don't have headers, change it to firstRow = 1

 

[matiquz]

Thanks again.

I do have headers and the data point starts from row 2. So there is no issue with that. The problem is that the macro is taking average of first 10 data points, missing average of second 10 data points, then again taking average of third 10 data points, and missing average of 4th 10 data points.

Therefore, the new data sheet has 250 rows, but it supposed to have 500 rows (takings average of every 10 rows from a total of 5000 rows should yield 500 rows).

 

[Peter_SSs]

Welcome to the MrExcel board!

Here is a more direct (& faster) way you may wish to also consider.
Assumptions are that
- data is in Sheet1 starting at A2,
- 29 columns
- number of rows determined from column A
- results into Sheet2

Sub GetAverages()
  Application.ScreenUpdating = False
  With Sheets("Sheet2")
    .UsedRange.ClearContents
    With .Range("A2").Resize(Sheets("Sheet1").Range("A" & Sheets("Sheet1").Rows.Count).End(xlUp).Row / 10 + 1, 29)
      .Formula = "=IFERROR(AVERAGE(INDEX(Sheet1!A:A,10*ROWS(A$2:A2)-8):INDEX(Sheet1!A:A,10*ROWS(A$2:A2)+1)),"""")"
      .Value = .Value
    End With
  End With
  Application.ScreenUpdating = True
End Sub

 

[matiquz]

It works perfect. Thank you Peter_SSs.

 

[Peter_SSs]

It works perfect. Thank you Peter_SSs.

Cheers. Thanks for letting us know.