AVERAGE PROBLEM

[oleg_v]

Hello
I need help with a macro:

i have a column of data in sheet1
column Q

-0.001 -0.001 0.002 0.000 0.000 -0.006 -0.001 -0.001 0.003 0.001 -0.004 -0.003 -0.001 -0.001 0.005 -0.004 0.004 -0.006 0.000 0.005 -0.008 -0.006 0.000 -0.006
i need that macro will calculate the average of each 4 consecutive parts starting with "Q2"

and the average results will paste in column "B" starting with "B2"

thanks

[GTO]

Try:

Sub exa()
Dim i As Long

i = 2

Do While Not i >= Cells(Rows.Count, "Q").End(xlUp).Row + 1
With Cells(Cells(Rows.Count, "B").End(xlUp).Offset(1).Row, "B")
.Formula = "=AVERAGE(" & Range(Cells(i, "Q"), Cells(i + 3, "Q")).Address & ")"
.Value = .Value
End With
i = i + 4
Loop
End Sub


Hope that helps,

Mark

[oleg_v]

Thanks it works
only 1 question how can i activate this macro from other sheet?

[GTO]

Thanks it works
only 1 question how can i activate this macro from other sheet?

I am not quite sure what you are asking refrence "...from the other sheet."

The code should really go in a Standard Module, not a sheet's module.

If you are asking how to make sure the code acts against a certain sheet, regardless of what sheet is active, the answer is to set a reference to the sheet and qualify range references. Something like:

Sub exa()
Dim i As Long
Dim wks As Worksheet

Set wks = ThisWorkbook.Worksheets("Sheet1")

i = 2

Do While Not i >= wks.Cells(Rows.Count, "Q").End(xlUp).Row + 1
With wks.Cells(wks.Cells(Rows.Count, "B").End(xlUp).Offset(1).Row, "B")
.Formula = "=AVERAGE(" & Range(wks.Cells(i, "Q"), wks.Cells(i + 3, "Q")).Address & ")"
.Value = .Value
End With
i = i + 4
Loop
End Sub


Hope that helps,

Mark