average if with time

[anjem]

I have data sorted by date(mm/dd/yy) and time(hh.mm.ss) and I am tryin
to write a function which will average every hour. How can I sor
through the times and compute an average without getting all the 2
clocks, for example, as opposed to the hour on one particular day
Thanks for the help,

Andrew Moo

 

[daniels012]

Need a little more detail. Maybe give me an example of what you have a
data and then tell me what results you are trying to recieve from tha
data!

michae

 

[Fred Smith]

I see your problem as having separate date and time cells. There's no need
to have separate cells -- you can have both a date and a time in a cell. I
would put the two fields together (=a1+b1), then average this new cell.

 

[anjem]

The data is all in integer format, so averaging wouldn't be a problem
It is data collected for every minute in the hour and what I want to d
is sum and average every hour of data. For example:

1 5/4/2002 6:36:00 14010 280
2 5/4/2002 6:37:00 230 11234
3 5/4/2002 6:38:00 10922 626

I want to average to find iterations per minute and total iteration
per hour. This is only three minutes of data, I want to do it for a
hour. Thank

 

[Peo Sjoblom]

If the times are in a separate column use

=AVERAGE(IF(A2:A20=DATE(2004,4,1),B2:B20))

where B2:B20 holds the times and A2:A20 the dates and the date you want to
average is 04/01/04

if they are in the same column

=AVERAGE(IF(INT(A2:A20)=DATE(2004,4,1),A2:A20))

both formulas entered with ctrl + shift & enter

format result as time

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[daniels012]

If you have times listed as you showed, then at the end of each hour
have a formula in the cell to the right for total and average.
=SUM(C7:C66)
=ROUND(AVERAGE(C7:C66),0)

This should help!
Michae