Grouping a Set of Data By Time Interval

[Jon]

Does anyone know the best way to group a set of Excel data by time interval
of x Seconds?

For example, if I have:
Time Count
0:16:24 1
0:16:40 1
0:16:49 1
0:16:51 2
0:17:02 1
0:17:11 1

I want to say, group and auto-sum the data for intervals of every 10 seconds
starting with the first time value. So I'd expect something like:
0:16:24 - 0:16:34 1
0:16:34 - 0:16:44 1
0:16:44 - 0:16:54 3
0:16:54 - 0:17:04 1
0:17:04 - 0:17:14 1

or I'll even take within a window of 10 seconds starting from each
successive value like:
0:16:24-0:16:34 1
0:16:40-0:16:49 2
0:16:51-0:17:01 2
0:17:02-0:17:12 2

Thanks for any help you can provide.

Jon

 

[Guest]

You need a couple of steps and I have assumed your raw data are in columns A
& B

First set up your time intervals in a column by typing the first time
0:16:24 in (say) E1.
In E2 put the formula
=E1+Time(0,0,10) and drag this down as far as required

Second.
In D1 type the formula
=SUM((A$1:A$100>=E1)*(A$1:A$100<E1+TIME(0,0,10))*(B$1:B$100))
It's an array so enter wit Ctrl+Shift+Enter.
Drag down to the length of column D and your done.

Mike

 

[Guest]

A B C D E

Time Count
0:16:24 1 =A2 =C2+10/(24*60*60) formula below
0:16:40 1 =D2 =C3+10/(24*60*60) drag down
0:16:49 1 =D3 drag down
0:16:51 2
0:17:02 1
0:17:11 1

Formula:
=SUMPRODUCT(--($A$2:$A$7>=C2),--($A$2:$A$7<D2),$B$2:$B$7)

Adjust ranges!

Regards,
Stefi