Min Max Average

[sambisu]

I have the following macro for taking a data series and extracting daily min/max/average. I would like to convert this to extract hourly min/max/average instead of daily, but not sure how.

Sub MinMaxAvg()


Dim Rng As Range, Dn As Range, n As Long, c As Long, K As Variant
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
Application.ScreenUpdating = False
For Each Dn In Rng
If Not .Exists(DateValue(Dn.Value)) Then
.Add DateValue(Dn.Value), Dn.Offset(, 1)
Else
Set .Item(DateValue(Dn.Value)) = Union(.Item(DateValue(Dn.Value)), Dn.Offset(, 1))
End If
Next
Range("E1:H1") = Array("Date", "Max", "Min", "Average")
c = 1
For Each K In .keys
c = c + 1
Cells(c, "E") = K
Cells(c, "F") = Application.Max(.Item(K))
Cells(c, "G") = Application.Min(.Item(K))
Cells(c, "H") = Application.Average(.Item(K))
Next K
End With
Application.ScreenUpdating = True




End Sub

 

[Roderick_E]

How long is a "day"? 24 hrs? If so you could divide by 24 before min/max/avg though this will not be truly accurate. Is your data by hour?

 

[sambisu]

Yes, 24 hours. My previous data was every 30 minutes and I was wanting to reduce to daily data. Now I have a different data set that has data points every 40 seconds or so and I want to reduce it to hourly data.

 

[sambisu]

Seems like I just need another term in there to test for the hour. So maybe like a subgrouping under each day? I've been messing around with it, but I can't quite figure out how to do that.