half hour data into hour data

[rokkarinn]

Hi all,

I have large data sets with half hour values that I need to average into hourly values. The data set spans quite a few years so I'm hoping for a relatively quick and easy solution. Here is an example of the data:

28.12.2012 01:30:00 0,1 2,4 2,5 127,6 28.12.2012 02:00:00 0 0,9 0,9 118,5 28.12.2012 02:30:00 0,2 2 2,2 121 <colgroup><col><col><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[sijpie]

Not sure exactly what output you am looking for.

If it is just the average, then there is no mathematical difference between the half-hour average and the hour average.

Or is it that you need to rebuild the table with only full hours showing?

Please explain clearly what the output should look like.

 

[rokkarinn]

Sorry for the unclear question,

I need to rebuild the table with only full hours showing, using the average from the two values in the hour. So, instead of having two data points for each hour I need one using the average of the two.

 

[sijpie]

Do you need to do this often, or just once? Do you want to use macro or formulas?

 

[rokkarinn]

I need to do this often, at least 10 times a year for different datasets. Either macros or formulas, anything that saves time

 

[sijpie]

OK, this macro should work. Read the comments at the start, as they mention the sheetnames used. If your sheetnames are differnt then amend them at the top of the code. The macro can be made to run even faster by reading the complete table into an array and writing it back in one, but the code below is pretty fast, it writes a row at a time, and pretty foolproof (i hope)

Option Explicit


Const WSINPUT = "HalfHours"
Const WSOUTPUT = "Compact"
'## set constants above to sheet names containing input (with half hours)
'## and output (full hours)


Sub CollapseHalfHours()
    Dim rIn As Range, rOut As Range
    Dim wsIn As Worksheet, wsOut As Worksheet
    Dim i As Long, j As Long, k As Integer
    Dim arAvg As Variant, arOut As Variant
    
    ReDim arOut(1 To 6)
    
    Set wsIn = Worksheets(WSINPUT)
    Set wsOut = Worksheets(WSOUTPUT)
    
    Set rIn = wsIn.Range("A1")
    Set rOut = wsOut.Range("A1") ' if the output needs to be appended some more code is required here
    
    Do While Not IsDate(rIn.Offset(i, 1).Text)
        ' get row of the first entry
        i = i + 1
    Loop
    If InStr(1, rIn.Offset(i, 1).Text, ":00:") Then
        ' the first entry is full hour
        ' so it has first half hour missing of the hour
        'Let's write this entry direct to our output, no averaging
        rOut.Resize(, 6).Value = rIn.Resize(, 6).Value
        i = i + 1
        j = j + 1
    End If
    Do While IsDate(rIn.Offset(i, 1).Text)
        ' load the next two rw in an array for quick calc and
        ' quick writing to sheet
        arAvg = rIn.Offset(i, 0).Resize(2, 6)
        For k = 3 To 6
            arOut(k) = (arAvg(1, k) + arAvg(2, k)) / 2
        Next k
        For k = 1 To 2
            arOut(k) = arAvg(2, k)
        Next k
        rOut.Offset(j, 0).Resize(1, 6).Value = arOut
        i = i + 2
        j = j + 1
    Loop
End Sub