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

 

[hjason315]

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

That's 6.85 right just formatted to Scientific?

It is formatted as Scientific. still couldn't figure out.
For this macro, is there any restrictions for the numbers in Col B ? The numbers I have are all contains 5 or 6 numbers after decimal point.

 

[hjason315]

It is formatted as Scientific. still couldn't figure out.
For this macro, is there any restrictions for the numbers in Col B ? The numbers I have are all contains 5 or 6 numbers after decimal point.

never mind, I think I found out the reason.
Missing "Range" when doing average
Let me try this again

 

[hjason315]

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

That's 6.85 right just formatted to Scientific?

Thank you so much. This macro works great! just need to add Range after Average.