Time averaging raw data

[Chris J Denver]

Hi newsgroup,

I have got a spreadsheet with 10 minute data as follows:

Timestamp;Value
01/01/12 00:00; 12
01/01/12 00:10; 10
01/01/12 00:20; 11
....

I would like to average this data into half hourly as follows

Timestamp;Value
01/01/12 00:30; 11

and am trying to write a macro to do this. Now this would be easy if
there were always three 10min data points to average, but the problem
is that often there is data missing, so instead there could be only 2,
1, or no 10min data points within one 30min timeframe. I would need
the macro to read the timestamp (MS Office format) and based on this
write averages for each 30min timeframe. And this is what I can't sort
out how to do...

Any help would be greatly appreciated!

Many thanks,

Chris

 

[James Ravenswood]

Say the active cell is on some timevalue. If the time difference between the first and third time is less than or equal to 30 minutes, average three values and exit. If the time difference between the first and second time is less than or equal to 30 minutes, then average the first and second values and exit. For example:


Sub TimeAverage()
Dim r As Range
Set r = ActiveCell

t1 = r.Value
t2 = r.Offset(1, 0).Value
t3 = r.Offset(2, 0).Value

v1 = r.Offset(0, 1).Value
v2 = r.Offset(1, 1).Value
v3 = r.Offset(2, 1).Value

If t3 - t1 <= 0.020833333 Then
MsgBox (v1 + v2 + v3) / 3
Exit Sub
End If

If t2 - t1 <= 0.020833333 Then
MsgBox (v1 + v2) / 2
Exit Sub
End If

MsgBox v1
End Sub

 

[christopher.j.denver]

Hi James,

Say the active cell is on some timevalue. If the time difference betweenthe first and third time is less than or equal to 30 minutes, average three values and exit. If the time difference between the first and second time is less than or equal to 30 minutes, then average the first and second values and exit.

Perfect, many thanks. I've now put that in a loop and it works fine!

Best,

Chris

 

[joeu2004]

I have got a spreadsheet with 10 minute data [....]
I would like to average this data into half hourly [....]
and am trying to write a macro to do this.
[....] the problem is that often there is data missing,
so instead there could be only 2, 1, or no 10min data
points within one 30min timeframe.

The following macro does not even assume that the original data is reported
at 10-minute multiples.

Option Explicit

Sub avgByHalfHour()
Dim v
Dim n As Long, m As Long
Dim i As Long, j As Long, k As Long
Dim tNext As Double, tLast As Double
Dim t As Double, tot As Double

' input 2-column data (date/time and number).
' in Excel, select n-by-2 range of data or upper-left
' corner of data before running macro.
' implicitly assume n>=2
v = Range(Selection, Selection.Offset(0, 1).End(xlDown))
n = UBound(v, 1)

' tNext = end of next 30-min interval.
' if v(1,1) is at a 30-min interval (xx:00 or xx:30),
' it will be included in average of next 30-min.
' tNext and tLast are integral minutes to avoid
' floating-point anomalies that might arise with
' normal date/time values
tNext = Int(Round(v(1, 1) * 1440) / 30 + 1) * 30
tLast = Int(Round(v(n, 1) * 1440 + 29) / 30) * 30
m = (tLast - tNext) / 30 + 1
ReDim res(1 To m, 1 To 2)

' assume no duplicate date/times
tot = 0: k = 0
i = 1: j = 0
Do While i <= n
t = Round(v(i, 1) * 1440)
If t <= tNext Then
tot = tot + v(i, 2)
k = k + 1
i = i + 1
Else
' use CDate(Format(...)) to avoid floating-point
' anomalies that might arise due to simply tNext/1440
j = j + 1
t = tNext / 1440
res(j, 1) = Int(t) + CDate(Format(t, "hh:mm"))
If k = 0 Then res(j, 2) = 0 Else res(j, 2) = tot / k
tot = 0: k = 0
tNext = tNext + 30
End If
Loop
If k > 0 Then
j = j + 1
t = tNext / 1440
res(j, 1) = Int(t) + CDate(Format(t, "hh:mm"))
res(j, 2) = tot / k
End If

' output into 2 columns to the right of
' 2-column input
'*******************************************
' *** adjust formats to suit your tastes ***
' ******************************************
With Selection.Offset(0, 2)
.Resize(j, 2).Value = res
With .Resize(j, 1)
.NumberFormat = "mm/dd/yyyy hh:mm"
.EntireColumn.AutoFit
End With
With .Offset(0, 1).Resize(j, 1)
.NumberFormat = "General"
.EntireColumn.UseStandardWidth = True
End With
End With
End Sub