How do I sample hourly data one time per day
 

I have hourly data from an instrument and would like to reduce the data set
(see example below) to one point per day, either by selecting a specific time
(e.g., 00:00) each day, or by averaging all of the values for one day. The
data files may contain more than one month's worth of data per file (i.e.,
quite large files).


8/11/2006 00:00 17.06
8/11/2006 01:00 17.05
8/11/2006 02:00 17.05
8/11/2006 03:00 17.05
8/11/2006 04:00 17.05
8/11/2006 05:00 17.05
8/11/2006 06:00 17.06
8/11/2006 07:00 17.06
8/11/2006 08:00 17.05
8/11/2006 09:00 17.06
8/11/2006 10:00 17.06
8/11/2006 11:00 17.06
8/11/2006 12:00 17.06
8/11/2006 13:00 17.06
8/11/2006 14:00 17.06
8/11/2006 15:00 17.06
8/11/2006 16:00 17.06
8/11/2006 17:00 17.06
8/11/2006 18:00 17.06
8/11/2006 19:00 17.05
8/11/2006 20:00 17.06
8/11/2006 21:00 17.06
8/11/2006 22:00 17.06
8/11/2006 23:00 17.06
8/12/2006 00:00 17.06
8/12/2006 01:00 17.05
8/12/2006 02:00 17.06
8/12/2006 03:00 17.06
8/12/2006 04:00 17.06
8/12/2006 05:00 17.06
8/12/2006 06:00 17.06
8/12/2006 07:00 17.06
8/12/2006 08:00 17.06

How do I sample hourly data one time per day
 

Get the average with

=AVERAGE(IF(A1:A100=--"2006-08-11",B1:B1000)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Once you have a full set of averages, you can select all the data, then
EditPastespecialValues, and delete the original data.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Doc Merkin" <Doc wrote in message
...
I have hourly data from an instrument and would like to reduce the data
set
(see example below) to one point per day, either by selecting a specific
time
(e.g., 00:00) each day, or by averaging all of the values for one day.
The
data files may contain more than one month's worth of data per file (i.e.,
quite large files).


8/11/2006 00:00 17.06
8/11/2006 01:00 17.05
8/11/2006 02:00 17.05
8/11/2006 03:00 17.05
8/11/2006 04:00 17.05
8/11/2006 05:00 17.05
8/11/2006 06:00 17.06
8/11/2006 07:00 17.06
8/11/2006 08:00 17.05
8/11/2006 09:00 17.06
8/11/2006 10:00 17.06
8/11/2006 11:00 17.06
8/11/2006 12:00 17.06
8/11/2006 13:00 17.06
8/11/2006 14:00 17.06
8/11/2006 15:00 17.06
8/11/2006 16:00 17.06
8/11/2006 17:00 17.06
8/11/2006 18:00 17.06
8/11/2006 19:00 17.05
8/11/2006 20:00 17.06
8/11/2006 21:00 17.06
8/11/2006 22:00 17.06
8/11/2006 23:00 17.06
8/12/2006 00:00 17.06
8/12/2006 01:00 17.05
8/12/2006 02:00 17.06
8/12/2006 03:00 17.06
8/12/2006 04:00 17.06
8/12/2006 05:00 17.06
8/12/2006 06:00 17.06
8/12/2006 07:00 17.06
8/12/2006 08:00 17.06

How do I sample hourly data one time per day
 

... or by averaging all of the values for one day.

One way to easily do the above ..

Assuming source data in cols A to C, from row1 down,
where col C = values

Put in D1:
=AVERAGE(OFFSET(INDIRECT("C"&ROW(A1)*24-24+1),,,24))
Copy down as far as required
(copy down by as many days as the data covers)

D1 returns the average of C1:C24 (day1)
D2 returns the average of C25:C48 (day2), and so on

This part of the expression: ROW(A1)*24-24+1
is left intentionally unsimplified to show the pattern
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Doc Merkin" wrote:
I have hourly data from an instrument and would like to reduce the data set
(see example below) to one point per day, either by selecting a specific time
(e.g., 00:00) each day, or by averaging all of the values for one day. The
data files may contain more than one month's worth of data per file (i.e.,
quite large files).


8/11/2006 00:00 17.06
8/11/2006 01:00 17.05
8/11/2006 02:00 17.05
8/11/2006 03:00 17.05
8/11/2006 04:00 17.05
8/11/2006 05:00 17.05
8/11/2006 06:00 17.06
8/11/2006 07:00 17.06
8/11/2006 08:00 17.05
8/11/2006 09:00 17.06
8/11/2006 10:00 17.06
8/11/2006 11:00 17.06
8/11/2006 12:00 17.06
8/11/2006 13:00 17.06
8/11/2006 14:00 17.06
8/11/2006 15:00 17.06
8/11/2006 16:00 17.06
8/11/2006 17:00 17.06
8/11/2006 18:00 17.06
8/11/2006 19:00 17.05
8/11/2006 20:00 17.06
8/11/2006 21:00 17.06
8/11/2006 22:00 17.06
8/11/2006 23:00 17.06
8/12/2006 00:00 17.06
8/12/2006 01:00 17.05
8/12/2006 02:00 17.06
8/12/2006 03:00 17.06
8/12/2006 04:00 17.06
8/12/2006 05:00 17.06
8/12/2006 06:00 17.06
8/12/2006 07:00 17.06
8/12/2006 08:00 17.06