Avearge n cells macro

[daverunt]

Hi,

I am looking for a macro to average every n cells in a column of about 100k samples and happy for the result to be in the adjacent column.

Anyone have one lying about or can point me to another site?
I've looked and can find plenty of formulas to do the job but not a macro.
I want to be able to modify the step/column and not write formulas to the worksheet every time.

 

[rpaulson]

may not be the most efficient method, but it should work

Sub do_Avg()

c = 2 ' column  number with data
n = 3 'step

tot = 0 'reset totaler
cnt = 0 'rest counter
For r = 1 To Cells(Rows.Count, c).End(xlUp).Row Step n
    tot = tot + Cells(r, c)
    cnt = cnt + 1
Next r

Cells(1, c + 1) = tot / cnt 'write average to nrxt adjacent colum in row 1

End Sub

hth,
Ross

 

[steve the fish]

Heres one i wrote. Certainly not fast if the step is small for 100k rows.

Set sh = Sheets("Sheet1")
myStartRow = 1
myCol = 1
myStep = 8

With sh
    .Columns(myCol + 1).ClearContents
    lr = .Cells(.Rows.Count, myCol).End(xlUp).Row
    If lr >= myStep + myStartRow - 1 Then
        a = myStartRow
        For i = myStep + myStartRow - 1 To lr Step myStep
            '.Cells(i, myCol + 1) = WorksheetFunction.Average(.Range(.Cells(a, myCol), .Cells(i, myCol))) 'This for values only
            .Cells(a, myCol + 1).Formula = "=AVERAGE(" & .Cells(a, myCol).Address & ":" & .Cells(i, myCol).Address & ")"
            a = a + myStep
        Next
    End If
End With

 

[daverunt]

Hi Gents,

Ross, I am after averaging every n cells not every nth cell in the column, which I think is what your code is doing?

Steve your code works great for the averaging of every n cells using the WorksheetFunction.Average line, so I used that with a slight change so that the result is contiguous in the next column.
It works fast enough for me over 90k+ rows with a step of below 10.

Many thanks both. I can push on now.

Here's the modified version of Steve's code if anyone is interested.

Sub do_Avg()

Set sh = Sheets("Sheet1")
myStartRow = 4
myCol = 2
myStep = 5

'iteration counter to be used as the row number of the output column
j = 4

With sh
    '.Columns(myCol + 1).ClearContents
    lr = .Cells(.Rows.Count, myCol).End(xlUp).Row
    If lr >= myStep + myStartRow - 1 Then
        a = myStartRow
        For i = myStep + myStartRow - 1 To lr Step myStep
            .Cells(j, myCol + 1) = WorksheetFunction.Average(.Range(.Cells(a, myCol), .Cells(i, myCol))) 'This for values only
            a = a + myStep
            j = j + 1
        Next
    End If
End With