Hourly average in excel VBA

[usercenter93]

Hello,

I am new to VBA in excel . I have a range of data like 11000 numbers .i want that it calculates the average of first 60 then next 60 till the end . I did some programming but that isint working . So can someone please help me with it .


Sub Hourlyaverage()
Sheets("DUT1_Test51_excel").Select
Range("T2").Select
Do Until Selection.Offset(0, -8).Value = ""
Selection.Formula = "=Average(selection.offset(0,-8):selection.offset(60,-8))"


Selection.Offset(1, 0).Select


Loop

ActiveCell.Offset(1, 0).Select



End Sub

 

[MickG]

Try this:-
Results in column "B"

[COLOR="Navy"]Sub[/COLOR] MG22Apr35
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] r [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]For[/COLOR] n = 1 To 11000
c = c + 1: Temp = Temp + Cells(n, 1)
    [COLOR="Navy"]If[/COLOR] c Mod 60 = 0 [COLOR="Navy"]Then[/COLOR]
        r = r + 1
        Cells(r, 2) = Temp / c
        c = 0
        Temp = 0
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
Cells(r, 2) = Temp / c
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[usercenter93]

hey thanks a lot for ur reply but that is somehow confusing for me . ten u please tell me whats wrong in this or maybe u can correct this .

Sub Hourlyaverage()
Dim i As Long, j As Long, k As Long, l As Long, m As Long

Sheets("DUT1_Test51_excel").Select

i = 3
j = 3
k = 63

Do While Cells(i, 12).Value <> ""
l = Cells(i, 12).Value
m = Cells(k, 12).Value

Cells(j, 20).Value = [Average (l : m)]
i = i + 60
j = j + 1
k = k + 60
Loop


End Sub

 

[MickG]

My code relates to data in column "A" with the results in column "B".
If my code works for you , it would be better if you tried to understand my code, and then relate that back to yours.