Hi, I would appreciate any assistance with the following challenge im having.

Dont think the title is clear, so here's what iv got. (sorry for rambling)

Have random data, mostly recorded every 121 seconds (2 minutes and 1 seconds) however this is not always the case.
I would like to use this recorded data and average the values half hourly (30min), only when valid;

Data in the following format:
Date Time Value
17/02/2014 00:00:00 x
17/02/2014 00:02:01 y
17/02/2014 00:04:07 z

Would like the average from 00:00:00- 00:29:59 represented as a number next to the time 0:00;
the average from 00:30:00 - 00:59:59 represented next to 0:30 etc etc.
Date Time Value
17/02/2014 00:00:00 =average from 00:00:00-00:29:59
17/02/2014 00:30:00 =average from 00:30:00-00:59:59 etc


I have used the following formula as a rough estimate but this does not compensate for any changes in the recording time::
=average(offset(C$1,(Row()-(Row(C$1))*15,,15,)) (some 400k data points in 9 cols) (how do i replace the 15 with count(valid cells)?

this formula assists with the general average for 15*intervals =+-30min (1 interval = +-2:01 minutes)

However i would like to only accept valid values; so I would like to skip random 0's:
If the preceding 2 cells are values and the following 2 cells are values then its just an odd recording error, so, it should ignore the random 0,but not ignore ALL 0's between time period.

I have played around with the iferror function but cant seem to get a standard formula or template to convert this random data into 30min average intervals, ignoring the occasional '0', (i cant seem to use count function to count the occasional '0' error, only all of them, i have tried to split up the time then average using a count function but have been unsuccessful).

Thanks..