Hourly Averages from 5 Minute Timestamps

[UseLessFuel]

Hello. I am struggling with what may be quite simple. I am working with timestamped data in dd-mmm-yyyy hh:mm:ss format, where a value occurs every 5 minutes (but not exactly). I require to obtain an hourly average for each and every hour, which would normally be an average of the previous 12 cells (since the data is in 5 minute intervals). Example below:
[TABLE="width: 500"]
<tbody>[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 15:42:15
[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.8[/TD]

</tbody>

[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 15:47:15
[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> </colgroup><tbody>
[TD="class: xl66, width: 99"]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.8[/TD]

</tbody>

[/TD]

[TD="class: xl66"][/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 15:52:16
[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.8[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 15:57:17[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.8[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 16:02:18[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.8[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 16:07:19[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.8[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 16:12:20[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.8[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 16:17:20[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.8[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 16:22:21[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.8[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 16:27:22[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.8[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 16:32:23[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.9[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 16:37:24
[/TD]

[TD="class: xl65, width: 172, align: right"][/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.9[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="80"></colgroup><tbody>
[TD="class: xl66, width: 80"]19.8[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]15-Apr-2011 16:42:25[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99"]19.9[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
This particular dataset has 228,000 rows, and while almost all of the time intervals are between 5 minutes exactly, and 5.0333 minutes, there are instances of data-gaps, ranging from one timestamp missing, up to 7 days of data missing (= 2,000 records missing - I use the comma separator as a thousand separator). Also, there may be instances, where an hour of data may take 13 records, instead of 12, due to the slightly irregular time interval gaps. In the Table above, the 3rd column contains the average of the previous 12 records. My goal is to ascertain whether hourly records would be sufficient, since the values in the second column seem to change so little. It looks like I will also need the Max and Min values from each hour: crikey, it does look complex. Using MS Excel 2010, and Windows 7 Professional.

I am not experienced in VBA, although I have tinkered with it in the past. Any ideas?
(I can use Formulas, such as Countifs, Sumifs etc. and also Named ranges)

 

[Pup Denab]

<tbody>
[TD="class: xl65"]Date[/TD]
[TD="class: xl66"]Record[/TD]
[TD="class: xl66, width: 77"]Your Result[/TD]
[TD="class: xl65, width: 163"]Ref[/TD]
[TD="class: xl66, width: 144"]Actual Average Result[/TD]
[TD="class: xl66, width: 89"]Max per hour[/TD]
[TD="class: xl66, width: 87"]Min per hour[/TD]

[TD="class: xl67, width: 111"]15/04/2011 15:42[/TD]
[TD="class: xl68, width: 50"]100.00[/TD]
[TD="class: xl68, width: 77"][/TD]
[TD="class: xl69"]201141515[/TD]
[TD="class: xl66"]32.00[/TD]
[TD="class: xl66"]100.00[/TD]
[TD="class: xl66"]0.00[/TD]

[TD="class: xl67, width: 111"]15/04/2011 15:47[/TD]
[TD="class: xl68, width: 50"]14.00[/TD]
[TD="class: xl68, width: 77"][/TD]
[TD="class: xl69"]201141515[/TD]
[TD="class: xl66"]32.00[/TD]
[TD="class: xl66"]100.00[/TD]
[TD="class: xl66"]0.00[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl68, width: 50"][/TD]
[TD="class: xl69"]1900100[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]0.00[/TD]
[TD="class: xl66"]0.00[/TD]

[TD="class: xl67, width: 111"]15/04/2011 15:52[/TD]
[TD="class: xl68, width: 50"]0.00[/TD]
[TD="class: xl68, width: 77"][/TD]
[TD="class: xl69"]201141515[/TD]
[TD="class: xl66"]32.00[/TD]
[TD="class: xl66"]100.00[/TD]
[TD="class: xl66"]0.00[/TD]

[TD="class: xl67, width: 111"]15/04/2011 15:57[/TD]
[TD="class: xl68, width: 50"]14.00[/TD]
[TD="class: xl68, width: 77"][/TD]
[TD="class: xl69"]201141515[/TD]
[TD="class: xl66"]32.00[/TD]
[TD="class: xl66"]100.00[/TD]
[TD="class: xl66"]0.00[/TD]

[TD="class: xl67, width: 111"]15/04/2011 16:02[/TD]
[TD="class: xl68, width: 50"]19.80[/TD]
[TD="class: xl68, width: 77"][/TD]
[TD="class: xl69"]201141516[/TD]
[TD="class: xl66"]19.83[/TD]
[TD="class: xl66"]19.90[/TD]
[TD="class: xl66"]19.80[/TD]

[TD="class: xl67, width: 111"]15/04/2011 16:07[/TD]
[TD="class: xl68, width: 50"]19.80[/TD]
[TD="class: xl68, width: 77"][/TD]
[TD="class: xl69"]201141516[/TD]
[TD="class: xl66"]19.83[/TD]
[TD="class: xl66"]19.90[/TD]
[TD="class: xl66"]19.80[/TD]

[TD="class: xl67, width: 111"]15/04/2011 16:12[/TD]
[TD="class: xl68, width: 50"]19.80[/TD]
[TD="class: xl68, width: 77"][/TD]
[TD="class: xl69"]201141516[/TD]
[TD="class: xl66"]19.83[/TD]
[TD="class: xl66"]19.90[/TD]
[TD="class: xl66"]19.80[/TD]

[TD="class: xl67, width: 111"]15/04/2011 16:17[/TD]
[TD="class: xl68, width: 50"]19.80[/TD]
[TD="class: xl68, width: 77"][/TD]
[TD="class: xl69"]201141516[/TD]
[TD="class: xl66"]19.83[/TD]
[TD="class: xl66"]19.90[/TD]
[TD="class: xl66"]19.80[/TD]

[TD="class: xl67, width: 111"]15/04/2011 16:22[/TD]
[TD="class: xl68, width: 50"]19.80[/TD]
[TD="class: xl68, width: 77"][/TD]
[TD="class: xl69"]201141516[/TD]
[TD="class: xl66"]19.83[/TD]
[TD="class: xl66"]19.90[/TD]
[TD="class: xl66"]19.80[/TD]

[TD="class: xl67, width: 111"]15/04/2011 16:27[/TD]
[TD="class: xl68, width: 50"]19.80[/TD]
[TD="class: xl68, width: 77"][/TD]
[TD="class: xl69"]201141516[/TD]
[TD="class: xl66"]19.83[/TD]
[TD="class: xl66"]19.90[/TD]
[TD="class: xl66"]19.80[/TD]

[TD="class: xl67, width: 111"]15/04/2011 16:32[/TD]
[TD="class: xl68, width: 50"]19.90[/TD]
[TD="class: xl68, width: 77"][/TD]
[TD="class: xl69"]201141516[/TD]
[TD="class: xl66"]19.83[/TD]
[TD="class: xl66"]19.90[/TD]
[TD="class: xl66"]19.80[/TD]

[TD="class: xl67, width: 111"]15/04/2011 16:37[/TD]
[TD="class: xl68, width: 50"]19.90[/TD]
[TD="class: xl68, width: 77"]19.80[/TD]
[TD="class: xl69"]201141516[/TD]
[TD="class: xl66"]19.83[/TD]
[TD="class: xl66"]19.90[/TD]
[TD="class: xl66"]19.80[/TD]

[TD="class: xl70, width: 111"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl69"]1900100[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]0.00[/TD]
[TD="class: xl66"]0.00[/TD]

[TD="class: xl67, width: 111"]15/04/2011 16:42[/TD]
[TD="class: xl68, width: 50"]19.90[/TD]
[TD="class: xl68, width: 77"][/TD]
[TD="class: xl69"]201141516[/TD]
[TD="class: xl66"]19.83[/TD]
[TD="class: xl66"]19.90[/TD]
[TD="class: xl66"]19.80[/TD]

</tbody>

HI Uselessfuel
I like this one, rarely do i use array formulas, but here you go, try this

Ref
YEAR(A2)&MONTH(A2)&DAY(A2)&HOUR(A2)

Actual Average Result
=IFERROR(AVERAGEIF($D$1:$D$16,D2,$B$1:$B$16),"")

Max per hour
=MAX(IF($B$1:$D$16=D2,$B$1:$B$16))
This will need to cnt shift enter to make an array formula with {} either side

Min per hour
=MIN(IF($B$1:$D$16=D2,$B$1:$B$16))
This will need to cnt shift enter to make an array formula with {} either side

If you are still having issues, I can upload to dropbox for you

 

[UseLessFuel]

Hi Pup Denab. Thanks for reply. I've been delayed, so, sorry about the late reply.

Looking at your post, briefly, I'm wondering where you get your first 4 records from? My, original, first 4 records were all 19.8 , while you have 100, 14, 0 and 14 again. Was this for demonstrative purposes? Will continue to (attempt to) disect.

Regards,

UseLess

 

[UseLessFuel]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Date Time
[/TD]
[TD]Record
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]06-Oct-2011 19:19:56[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99, align: right"]18.2[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]06-Oct-2011 19:24:57[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99, align: right"]18.2[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]14-Oct-2011 13:56:50[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99, align: right"]19.8
[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<colgroup><col width="172"></colgroup><tbody>
[TD="class: xl65, width: 172, align: right"]14-Oct-2011 14:01:51[/TD]

</tbody>

[/TD]
[TD]

<colgroup><col width="99"></colgroup><tbody>
[TD="class: xl66, width: 99, align: right"]19.8[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

Hi Pup.

I understand what you have done, and it works well, although with your data, i.e. the different first 4 records, I get 32 in cell E4, whereas you have 0 . Why is this?
However, (and it was my omission from the original post), my data does have gaps, but the gaps do not take up any rows. Example above shows nearly 8 full days missing, but the data is still contained in consecutive rows.

Thanks so far Pup. Again, I may take a while to get back to you.

Regards,

UseLess

 

[Pup Denab]

Hi
When I copied you data & pasted into excel the gaps where their, so I just left them, I changed the data to test the calculations, sorry for the confusion, however it should work better without gaps. No problem we are here to help
Regards
Pup