Data Reduction using Rolling Average

**thomas.r** · 04-18-2013, 07:33 PM

I am try to reduce data using a rolling average. I am suppose to take the average of rows 1,2,4,5 and compare it to the middle row which is 3. If the average of rows 1,2,4,5 is greater than row 3 by more than 8 then I select it. I then continue the process this time if the average of rows 2,3,5,6 is greater than 4 then select it. I am trying to write a simple code which I need help. Some of my syntax might be wrong. I am a VBA newbie. I will like to copy the data to a different sheet. my code is incomplete

Sub Macro1()
Application.ScreenUpdating = False
Sheets("Data").Select
Lastrow = Range("A65536").End(xlUp).Row

For t = 3 To Lastrow
Sheets("Data").Select
avg(t) = (cell(t-2,2) + cell(t-1,2) + cell(t+2,2) + cell(t+1,2))/4
If ((avg(t)-cell(t,2))>=8) Or ((avg(t)-cell(t,2))>=-8) Then

**tehneXus** · 04-18-2013, 08:07 PM

Hi,

please test this on your data:

Option Explicit

Sub Macro1()

    Application.ScreenUpdating = False
    
    Dim LastRow As Long, t As Long
    Dim avgt As Double
    
    With ThisWorkbook.Worksheets("Data")
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        
        For t = 3 To LastRow

            avgt = (.Cells(t - 2, 2).Value + .Cells(t - 1, 2).Value + .Cells(t + 1, 2).Value + .Cells(t + 2, 2).Value) / 4
            Debug.Print avgt - .Cells(t, 2).Value
            If avgt - .Cells(t, 2).Value <= 8 And avgt - .Cells(t, 2) >= -8 Then
                MsgBox t - 2 & " to " & t + 2 & ": " & avgt
                GoTo ExitT
            End If
            
        Next t
ExitT:
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Regards

**thomas.r** · 04-18-2013, 08:55 PM

It works but it only takes the average of the first 5 rows. I am trying to write a code that will calculate the rolling average of the rows from (1 to 5) then (2 to 6), (3 to 7) etc... when the average is calculated it will then be compared to the median row 3,4, and 5 respectively.If the median row is greater than the value 8 or -8 when compared to the average of the other rows, the median row is then copied to another sheet

**tehneXus** · 04-19-2013, 01:10 PM

Hi,

please make sure to have a worksheet "Result" in the workbook where the avg will be written into columns A and B if the difference of the avg and the median is not greater than 8.

Sub Macro1()

    Application.ScreenUpdating = False
    
    Dim LastRow As Long, t As Long, q As Long
    Dim avgt As Double
    
    With ThisWorkbook.Worksheets("Data")
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        
        For t = 3 To LastRow - 2

            avgt = (.Cells(t - 2, 2).Value + .Cells(t - 1, 2).Value + .Cells(t + 1, 2).Value + .Cells(t + 2, 2).Value) / 4
'            Debug.Print avgt - .Cells(t, 2).Value
            If avgt - .Cells(t, 2).Value <= 8 And avgt - .Cells(t, 2) >= -8 Then
'                MsgBox t - 2 & " to " & t + 2 & ": " & avgt
                q = ThisWorkbook.Worksheets("Result").Cells(ThisWorkbook.Worksheets("Result").Rows.Count, 1).End(xlUp).Row + 1
                ThisWorkbook.Worksheets("Result").Cells(q, 1).Value = t - 2 & " to " & t + 2
                ThisWorkbook.Worksheets("Result").Cells(q, 2).Value = avgt
            End If
            
        Next t

    End With
    
    Application.ScreenUpdating = True
    
End Sub

Regards