Dividing data into bins and calculating the averages

Hi everyone,

I have huge amount of data with me. I have speeds of vehicles generated at random times(for 24 hrs) in a column. Now I need to divide the data into bins of 300 secs (5 mins) and calculate the average value of speeds for every bin.

The problem I am facing is that the bin size varies from time to time due to randomness in data. So this makes it difficult to the application of a single formula to calculate the average of speeds for vehicles in a particular bin. I can do this manually by seeing the times and calculating the average for every 5 min interval, but it would take a lot of time as the dat is huge.

Can anyone please let me know the procedure to do this?

Thanks
sashi

PS: please let me know if the question is not clear

Hi everyone,

I have huge amount of data with me. I have speeds of vehicles generated at random times(for 24 hrs) in a column. Now I need to divide the data into bins of 300 secs (5 mins) and calculate the average value of speeds for every bin.

The problem I am facing is that the bin size varies from time to time due to randomness in data. So this makes it difficult to the application of a single formula to calculate the average of speeds for vehicles in a particular bin. I can do this manually by seeing the times and calculating the average for every 5 min interval, but it would take a lot of time as the dat is huge.

Can anyone please let me know the procedure to do this?

Thanks
sashi

PS: please let me know if the question is not clear

Post some data (like 20 minutes worth) and I or someone else will provide a formula.

Threads merged.

sashi, please read the Forum Rules, and don't post the same thread in two forums.

Hi Sashi,

you could try this approach. Name the area where the times are as "time" and the area where speeds are as "speed". Both areas has to have exactly the same amount of rows (as I assume, that you have).

Now make the bins. In my example I have 0:00:00 in G2, 0:00:05 in G3 and so on until G290 where I have 0:00:00 again.

Now put this formula in H2 and copy it all the way down to H289:

Please Login or Register  to view this content.

Hope this helps.

- Asser

Or, using Jazzer's layout and an array formula,

=AVERAGE(IF( (time>=G2) * (time < G3), speed) )

I think the second relation should be "<" rather than "<=" to avoid counting the same speed in two time periods.

will this work with version 2003?