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
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