need macro to get date and export the average

[hjason315]

Hello all,

I have a huge data file that contains 16000 rows in Col B. And I need to scan 200 rows at a time, and IF the Standard Deviation is LESS THAN 0.05, export the average of these 200 numbers to Col E.

I do not have much VBA knowledge to write a code that can scan 200 rows at a time. Is there anyone can help me please Your help would be greatly appreciated!

Jason

 

[Comfy]

Definitely possible just a cpl of questions.

When you say 200 rows at a time do you mean:

80 sets of 200. 1600 rows/200 = 80. (Maximum of 80 results)

Or

For every Row + 199 lines below it. (Maximum of 1400 results)

 

[hjason315]

Definitely possible just a cpl of questions.

When you say 200 rows at a time do you mean:

80 sets of 200. 1600 rows/200 = 80. (Maximum of 80 results)

Or

For every Row + 199 lines below it. (Maximum of 1400 results)

I meant the latter one "For every Row + 199 lines below it." For example, 1-200, then 2-201 and so on until 1401-1600

Thanks for your help.

 

[hjason315]

Actually its 16000, sorry. but you get the idea. the maximum result would be 15800 I guess, but I only want the average of those SD <0.05 to return. Thanks again

 

[Comfy]

Sub CalcStDev()
Dim i As Long, LastRow As Long
Dim stDev As Double

LastRow = Range("B65536").End(xlUp).Row - 199
If Not LastRow < 1 Then
For i = 1 To LastRow
If stDev = Application.WorksheetFunction.stDev(Range(Cells(i, 2), Cells(i + 199, 2))) < 0.05 Then
    Cells(i, 5).Formula = "Rows " & i & " to " & i + 199 & " average is: " & Application.WorksheetFunction.Average(Cells(i, 2), Cells(i + 199, 2))
End If
Next i
End If
End Sub

Try that

 

[hjason315]

Sub CalcStDev()
Dim i As Long, LastRow As Long
Dim stDev As Double
 
LastRow = Range("B65536").End(xlUp).Row - 199
If Not LastRow < 1 Then
For i = 1 To LastRow
If stDev = Application.WorksheetFunction.stDev(Range(Cells(i, 2), Cells(i + 199, 2))) < 0.05 Then
    Cells(i, 5).Formula = "Rows " & i & " to " & i + 199 & " average is: " & Application.WorksheetFunction.Average(Cells(i, 2), Cells(i + 199, 2))
End If
Next i
End If
End Sub

Try that

Seems close but not quite correct. It returns every number (average) instead of those stdev less than 0.05. I made the stdev even smaller to check, but it still returns every number. Just one more thing, those number might be negative

 

[Comfy]

A problem with VBA not evaluating stDev properly in the If statement.

Corrected:

Sub CalcStDev()
Dim i As Long, LastRow As Long
Dim stDev As Double
 
LastRow = Range("B65536").End(xlUp).Row - 199
If Not LastRow < 1 Then
For i = 1 To LastRow
stDev = Application.WorksheetFunction.stDev(Range(Cells(i, 2), Cells(i + 199, 2)))
If stDev < 0.05 Then
    Cells(i, 5).Formula = "Rows " & i & " to " & i + 199 & " average is: " & Application.WorksheetFunction.Average(Cells(i, 2), Cells(i + 199, 2))
End If
Next i
End If
End Sub

 

[hjason315]

A problem with VBA not evaluating stDev properly in the If statement.

Corrected:

Sub CalcStDev()
Dim i As Long, LastRow As Long
Dim stDev As Double
 
LastRow = Range("B65536").End(xlUp).Row - 199
If Not LastRow < 1 Then
For i = 1 To LastRow
stDev = Application.WorksheetFunction.stDev(Range(Cells(i, 2), Cells(i + 199, 2)))
If stDev < 0.05 Then
    Cells(i, 5).Formula = "Rows " & i & " to " & i + 199 & " average is: " & Application.WorksheetFunction.Average(Cells(i, 2), Cells(i + 199, 2))
End If
Next i
End If
End Sub

Now it works great, except the average number it returns doesn't seem correct. It is close but not excatly the same when I check using average formula.

Rows 1503 to 1702 average is: 7.002915
=AVERAGE(B1503:B1702) which is 6.85E+00

Rows 1504 to 1703 average is: 6.97415
=AVERAGE(B1504:B1703) which is 6.85E+00

Do you know any possible reason? Thanks!

 

[Comfy]

Ignore

 

[Comfy]

I don't. Try this:

Sub CalcStDev()
Dim i As Long, LastRow As Long
Dim stDev As Double, stAverage As Double
 
LastRow = Range("B65536").End(xlUp).Row - 199
If Not LastRow < 1 Then
For i = 1 To LastRow
stDev = Application.WorksheetFunction.stDev(Range(Cells(i, 2), Cells(i + 199, 2)))
If stDev < 0.05 Then
stAverage = Application.WorksheetFunction.Average(Cells(i, 2), Cells(i + 199, 2))
    Cells(i, 5).Formula = "Rows " & i & " to " & i + 199 & " average is: " & stAverage
End If
Next i
End If
End Sub

6.85E+00

That's 6.85 right just formatted to Scientific?