How do I sample hourly data one time per day

[Guest]

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

 

[Bob Phillips]

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
Edit>Pastespecial>Values, and delete the original data.

--
HTH

Bob Phillips

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

 

[Guest]

... 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