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

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

Hi James,

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.

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

Best,

Chris