Averaging set of data based on the time
 

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.

Averaging set of data based on the time
 

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" wrote:

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.

Averaging set of data based on the time
 

On Mar 27, 5:18*pm, Mike H wrote:
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" wrote:
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.- Hide quoted text -

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