Averaging set of data based on the time

[voyager1]

I am new to Excel spreadsheet functions. I would like to average the
data over a minute for a 24 hour period. How can I do this?

The data looks like this:

Col A Column B Column C
09/02/2007 12:26:00 PM -11.729
09/02/2007 12:26:02 PM -11.616
09/02/2007 12:26:04 PM -16.147
..
..
..
..
..
09/02/2007 12:27:00 PM 46.3466

Once I hit 12:27:00, I would perform the average and then start the
next period with 12:27:00 and go until I hit 12:28. I hope I am clear
on what I would like to accomplish. Thanks for your help.

 

[Mike H]

Hi I assume were averaging column C

Put this in a cell and commit Ctrl+Shift+Enter and drag down as required

=AVERAGE(IF(($B$1:B$200>=$B$1+TIME(0,ROW(A1)-1,0))*($B$1:$B$200<$B$1+TIME(0,ROW(A1),0)),$C$1:$C$200))

with 12:26 in B1
The first cell will average 12:26 - 12:27 and the next 12:27 - 12:28 etc

Mike

 

[voyager1]

Hi I assume were averaging column C

Put this in a cell and commit Ctrl+Shift+Enter and drag down as required

=AVERAGE(IF(($B$1:B$200>=$B$1+TIME(0,ROW(A1)-1,0))*($B$1:$B$200<$B$1+TIME(0­,ROW(A1),0)),$C$1:$C$200))

with 12:26 in B1
The first cell will average 12:26 - 12:27 and the next 12:27 - 12:28 etc

Mike








- Show quoted text -

You are correct. I am averaging column C. Your solution worked
partially. It did not work for the entire 24 hour period. When I put
the equation in the cell where the average needs to occur, it works.
If I highlight a whole range of cell and copy the formula into each
cell, it doesn't work. Am I doing something wrong? Thanks for your
help.