24hr moving average
 

I need to calculate 24hr moving averages of several columns of imported data
(see example below).
Column A has date and time the data is collected, the data is initally in
approx. 15min cycles, but at the end of the data (about row 1100) data is in
hourly cycles. Occasionally the imported data does not record a date or time
and occasionally, cycles of data may be missed for various reasons.

Example:
Date and Time Elevation (mm) RL 24hr Av (mid)
5/11/2005 10:42 -2.7
5/11/2005 10:51 -0.5
5/11/2005 11:05 -0.5
5/11/2005 11:19 -0.8
5/11/2005 11:34 -0.3
5/11/2005 11:49 0
5/11/2005 12:12 -0.1
5/11/2005 12:26 -0.5
5/11/2005 12:40 -0.6

Note: date is Australian format = dd/mm/yyyy

The data is extensive and there is considerable scatter so I need to try and
develop a way that will accurately calculate the 24 hour moving average, that
accounts for the above irregularities and that can somehow be incorporated
into a dragable formula. Ultimately this will be plotted in a graph.

Is this possible in a formula??

cheers,
Nadia

24hr moving average
 

If your data begins in row 2, with date/time in A and the observation in B,
the following formula should give you the average of all observations in the
24 hours ending with the time in A2. (As it's set up, if your ending time is
1pm today, it will EXCLUDE the observation made at exactly 1pm yesterday;
change the 1st and 3rd comparison operators to = if you want to include
it).
=(SUMIF(A:A,""& A2-1,B:B)-SUMIF(A:A,"" & A2,B:B))/(COUNTIF(A:A,""&
A2-1)-COUNTIF(A:A,"" & A2))
HTH. --Bruce

"Nadia" wrote:

I need to calculate 24hr moving averages of several columns of imported data
(see example below).
Column A has date and time the data is collected, the data is initally in
approx. 15min cycles, but at the end of the data (about row 1100) data is in
hourly cycles. Occasionally the imported data does not record a date or time
and occasionally, cycles of data may be missed for various reasons.

Example:
Date and Time Elevation (mm) RL 24hr Av (mid)
5/11/2005 10:42 -2.7
5/11/2005 10:51 -0.5
5/11/2005 11:05 -0.5
5/11/2005 11:19 -0.8
5/11/2005 11:34 -0.3
5/11/2005 11:49 0
5/11/2005 12:12 -0.1
5/11/2005 12:26 -0.5
5/11/2005 12:40 -0.6

Note: date is Australian format = dd/mm/yyyy

The data is extensive and there is considerable scatter so I need to try and
develop a way that will accurately calculate the 24 hour moving average, that
accounts for the above irregularities and that can somehow be incorporated
into a dragable formula. Ultimately this will be plotted in a graph.

Is this possible in a formula??

cheers,
Nadia