Max of close values

[rdubs]

Hi there, I hope someone can help.

I have data points for time/speed of moving objects.
The datapoints are clustered within short periods (approx 1.5 seconds), with differing time intervals between each cluster.
I need to find the max value within in a cluster, and also count the clusters.
I tried grouping, but this groups at regular intervals, sometimes splitting the clusters.
TIA

Speed clusters.xlsx
	A	B	C	D	E	F
1
2	time	speed	time as number
3	08:01:11.501	7.84	44833.3341608912
4	08:01:11.550	8.08	44833.3341614583
5	08:01:11.606	14.28	44833.3341621065
6	08:01:11.649	17.67	44833.3341626042
7	08:01:11.699	7.48	44833.3341631829
8	08:01:11.792	9.55	44833.3341642593
9	08:01:11.862	8.72	44833.3341650694
10	08:01:11.917	14.68	44833.3341657060
11	08:01:11.961	11.84	44833.3341662153
12	08:01:12.015	17.96	44833.3341668403
13	08:01:12.071	17.88	44833.3341674884
14	08:01:12.114	17.49	44833.3341679861
15	08:01:12.156	18.81	44833.3341684722
16	08:01:12.196	21.36	44833.3341689352
17	08:01:12.238	14.21	44833.3341694213
18	08:01:12.280	19.54	44833.3341699074		Ungrouped
19	08:01:12.333	19.93	44833.3341705208		Time	Max. of speed
20	08:01:12.385	19.61	44833.3341711227		44833.3341608912	7.84
21	08:01:12.439	19.71	44833.3341717477		44833.3341614583	8.08
22	08:01:12.578	19.97	44833.3341733565		44833.3341621065	14.28
23	08:01:12.676	16.08	44833.3341744907		44833.3341626042	17.67
24	08:01:12.723	21.13	44833.3341750347		44833.3341631829	7.48
25	08:01:12.786	15.74	44833.3341757639		44833.3341642593	9.55
26	08:01:12.830	20.70	44833.3341762731		44833.3341650694	8.72
27	08:01:35.986	10.02	44833.3344442824		44833.3341657060	14.68
28	08:01:36.031	21.38	44833.3344448032		44833.3341662153	11.84
29	08:01:36.071	16.69	44833.3344452662		44833.3341668403	17.96
30	08:01:36.139	14.56	44833.3344460532		44833.3341674884	17.88
31	08:01:36.178	26.20	44833.3344465046		44833.3341679861	17.49
32	08:01:36.245	27.22	44833.3344472801		44833.3341684722	18.81
33	08:01:36.292	29.41	44833.3344478241		44833.3341689352	21.36
34	08:01:36.331	21.10	44833.3344482755		44833.3341694213	14.21
35	08:01:36.377	27.31	44833.3344488079		44833.3341699074	19.54
36	08:01:36.415	32.64	44833.3344492477		44833.3341705208	19.93
37	08:01:36.473	28.13	44833.3344499190		44833.3341711227	19.61
38	08:01:36.523	37.97	44833.3344504977		44833.3341717477	19.71
39	08:01:36.566	29.52	44833.3344509954		44833.3341733565	19.97
40	08:01:36.707	37.97	44833.3344526273		44833.3341744907	16.08
41	08:02:07.414	5.85	44833.3348080324		44833.3341750347	21.13
42	08:02:07.461	13.73	44833.3348085764		44833.3341757639	15.74
43	08:02:07.509	10.66	44833.3348091319		44833.3341762731	20.70
44	08:02:07.594	12.22	44833.3348101157		44833.3344442824	10.02
45	08:02:07.642	15.21	44833.3348106713		44833.3344448032	21.38
46	08:02:07.691	13.56	44833.3348112384		44833.3344452662	16.69
47	08:02:07.769	8.21	44833.3348121412		44833.3344460532	14.56
48	08:02:07.918	7.05	44833.3348138657		44833.3344465046	26.20
49	08:02:07.965	8.71	44833.3348144097		44833.3344472801	27.22
50	08:02:08.025	10.69	44833.3348151042		44833.3344478241	29.41
51	08:02:08.103	16.17	44833.3348160069		44833.3344482755	21.10
52	08:02:08.145	13.23	44833.3348164931		44833.3344488079	27.31
53	08:02:08.197	16.61	44833.3348170949		44833.3344492477	32.64
54	08:02:08.239	16.47	44833.3348175810		44833.3344499190	28.13
55	08:02:08.301	15.96	44833.3348182986		44833.3344504977	37.97
56	08:02:08.345	16.01	44833.3348188079		44833.3344509954	29.52
57	08:02:08.402	18.23	44833.3348194676		44833.3344526273	37.97
58	08:02:08.455	18.61	44833.3348200810		44833.3348080324	5.85
59	08:02:08.517	18.27	44833.3348207986		44833.3348085764	13.73
60	08:02:08.558	18.74	44833.3348212732		44833.3348091319	10.66
61	08:02:08.600	18.85	44833.3348217593		44833.3348101157	12.22
62	08:02:08.648	19.30	44833.3348223148		44833.3348106713	15.21
63	08:02:08.690	18.89	44833.3348228009		44833.3348112384	13.56
64	08:02:08.731	19.51	44833.3348232755		44833.3348121412	8.21
65	08:02:08.774	19.47	44833.3348237731		44833.3348138657	7.05
66	08:02:08.816	19.37	44833.3348242593		44833.3348144097	8.71
67	08:02:08.874	19.85	44833.3348249306		44833.3348151042	10.69
68	08:02:08.918	19.93	44833.3348254398		44833.3348160069	16.17
69	08:02:09.030	20.51	44833.3348267361		44833.3348164931	13.23
70	08:02:09.071	20.13	44833.3348272107		44833.3348170949	16.61
71	08:02:09.126	20.25	44833.3348278472		44833.3348175810	16.47
72	08:02:09.199	12.35	44833.3348286921		44833.3348182986	15.96
73	08:02:52.964	19.80	44833.3353352315		44833.3348188079	16.01
74	08:02:53.024	12.35	44833.3353359259		44833.3348194676	18.23
75	08:02:53.284	21.16	44833.3353389352		44833.3348200810	18.61
76	08:02:53.331	20.05	44833.3353394792		44833.3348207986	18.27
77	08:02:53.390	14.77	44833.3353401620		44833.3348212732	18.74
78	08:02:53.459	20.25	44833.3353409606		44833.3348217593	18.85
79	08:02:53.516	19.90	44833.3353416204		44833.3348223148	19.30
80	08:02:53.583	15.38	44833.3353423958		44833.3348228009	18.89
81	08:02:53.667	18.57	44833.3353433681		44833.3348232755	19.51
82	08:02:53.711	15.28	44833.3353438773		44833.3348237731	19.47
83	08:02:53.752	18.29	44833.3353443519		44833.3348242593	19.37
84	08:02:53.794	16.99	44833.3353448380		44833.3348249306	19.85
85	08:02:53.837	15.32	44833.3353453356		44833.3348254398	19.93
86	08:02:53.883	12.28	44833.3353458681		44833.3348267361	20.51
87	08:02:53.931	8.63	44833.3353464236		44833.3348272107	20.13
88	08:02:54.017	10.41	44833.3353474190		44833.3348278472	20.25
89	08:02:54.098	6.02	44833.3353483565		44833.3348286921	12.35
90	08:02:54.171	13.89	44833.3353492014		44833.3353352315	19.80
91	08:02:54.221	8.13	44833.3353497801		44833.3353359259	12.35
92	08:02:54.282	11.29	44833.3353504861		44833.3353389352	21.16
93	08:02:54.334	6.45	44833.3353510880		44833.3353394792	20.05
94	08:03:02.960	21.22	44833.3354509259		44833.3353401620	14.77
95	08:03:03.181	20.70	44833.3354534838		44833.3353409606	20.25
96	08:03:03.235	20.97	44833.3354541088		44833.3353416204	19.90
97	08:03:03.276	21.02	44833.3354545833		44833.3353423958	15.38
98	08:03:03.321	20.30	44833.3354551042		44833.3353433681	18.57
99	08:03:03.378	20.35	44833.3354557639		44833.3353438773	15.28
100	08:03:03.434	20.41	44833.3354564120		44833.3353443519	18.29
101	08:03:03.476	19.75	44833.3354568981		44833.3353448380	16.99
102	08:03:03.520	17.63	44833.3354574074		44833.3353453356	15.32
103	08:03:03.564	14.26	44833.3354579167		44833.3353458681	12.28
104	08:03:03.621	16.03	44833.3354585764		44833.3353464236	8.63
105	08:03:03.663	15.96	44833.3354590625		44833.3353474190	10.41
106	08:03:03.717	15.61	44833.3354596875		44833.3353483565	6.02
107	08:03:03.774	16.01	44833.3354603472		44833.3353492014	13.89
108	08:03:03.819	10.60	44833.3354608681		44833.3353497801	8.13
109	08:03:03.861	12.99	44833.3354613542		44833.3353504861	11.29
110	08:03:03.908	9.34	44833.3354618981		44833.3353510880	6.45
111	08:03:03.965	6.54	44833.3354625579		44833.3354509259	21.22
112	08:03:04.028	17.38	44833.3354632870		44833.3354534838	20.70
113	08:03:04.085	19.23	44833.3354639468		44833.3354541088	20.97
114	08:03:04.131	12.75	44833.3354644792		44833.3354545833	21.02
115	08:03:04.177	13.95	44833.3354650116		44833.3354551042	20.30
116	08:03:04.221	13.62	44833.3354655208		44833.3354557639	20.35
117	08:03:04.275	6.83	44833.3354661458		44833.3354564120	20.41
118	08:03:12.922	5.85	44833.3355662269		44833.3354568981	19.75
119	08:03:12.976	8.73	44833.3355668518		44833.3354574074	17.63
120	08:03:13.028	9.24	44833.3355674537		44833.3354579167	14.26
121	08:03:13.076	12.28	44833.3355680093		44833.3354585764	16.03
122	08:03:13.125	13.73	44833.3355685764		44833.3354590625	15.96
123	08:03:13.183	7.47	44833.3355692477		44833.3354596875	15.61
124	08:03:13.369	7.99	44833.3355714005		44833.3354603472	16.01
125	08:03:13.418	10.09	44833.3355719676		44833.3354608681	10.60
126	08:03:13.489	6.41	44833.3355727894		44833.3354613542	12.99
127	08:03:13.533	13.04	44833.3355732986		44833.3354618981	9.34
128	08:03:13.600	12.30	44833.3355740741		44833.3354625579	6.54
129	08:03:13.647	12.54	44833.3355746181		44833.3354632870	17.38
130	08:03:13.698	10.20	44833.3355752083		44833.3354639468	19.23
131	08:03:13.742	12.83	44833.3355757176		44833.3354644792	12.75
132	08:03:13.797	11.41	44833.3355763542		44833.3354650116	13.95
133	08:03:13.888	14.05	44833.3355774074		44833.3354655208	13.62
134	08:03:13.932	14.04	44833.3355779167		44833.3354661458	6.83
135	08:03:13.990	14.22	44833.3355785880		44833.3355662269	5.85
136	08:03:14.052	14.53	44833.3355793056		44833.3355668518	8.73
137	08:03:14.147	14.71	44833.3355804051		44833.3355674537	9.24
138	08:03:14.193	15.19	44833.3355809375		44833.3355680093	12.28
139	08:03:14.269	15.50	44833.3355818171		44833.3355685764	13.73
140	08:03:14.382	15.47	44833.3355831250		44833.3355692477	7.47
141	08:03:14.512	15.49	44833.3355846296		44833.3355714005	7.99
142	08:03:14.605	15.49	44833.3355857060		44833.3355719676	10.09
143	08:03:14.741	15.34	44833.3355872801		44833.3355727894	6.41
144	08:03:34.803	6.83	44833.3358194792		44833.3355732986	13.04
145	08:03:34.860	10.14	44833.3358201389		44833.3355740741	12.30
146	08:03:34.921	12.46	44833.3358208449		44833.3355746181	12.54
147	08:03:34.989	7.39	44833.3358216319		44833.3355752083	10.20
148	08:03:35.037	8.43	44833.3358221875		44833.3355757176	12.83
149	08:03:35.139	7.44	44833.3358233681		44833.3355763542	11.41
150	08:03:35.186	8.91	44833.3358239120		44833.3355774074	14.05
151	08:03:35.231	10.54	44833.3358244329		44833.3355779167	14.04
152	08:03:35.291	15.43	44833.3358251273		44833.3355785880	14.22
153	08:03:35.334	13.75	44833.3358256250		44833.3355793056	14.53
154	08:03:35.375	15.78	44833.3358260995		44833.3355804051	14.71
155	08:03:35.418	13.95	44833.3358265972		44833.3355809375	15.19
156	08:03:35.495	15.28	44833.3358274884		44833.3355818171	15.50
157	08:03:35.554	20.59	44833.3358281713		44833.3355831250	15.47
158	08:03:35.604	20.10	44833.3358287500		44833.3355846296	15.49
159	08:03:35.667	20.75	44833.3358294792		44833.3355857060	15.49
160	08:03:35.719	21.50	44833.3358300810		44833.3355872801	15.34
161	08:03:35.797	21.56	44833.3358309838		44833.3358194792	6.83
162	08:03:35.842	21.73	44833.3358315046		44833.3358201389	10.14
163	08:03:35.889	21.67	44833.3358320486		44833.3358208449	12.46
164	08:03:36.007	22.34	44833.3358334144		44833.3358216319	7.39
165	08:03:36.047	21.97	44833.3358338773		44833.3358221875	8.43
166	08:03:36.120	22.56	44833.3358347222		44833.3358233681	7.44
167	08:03:36.227	22.56	44833.3358359606		44833.3358239120	8.91
168	08:04:10.650	23.48	44833.3362343750		44833.3358244329	10.54
169	08:04:10.698	17.10	44833.3362349306		44833.3358251273	15.43
170	08:04:10.738	22.00	44833.3362353935		44833.3358256250	13.75
171	08:04:10.794	21.82	44833.3362360417		44833.3358260995	15.78
172	08:04:10.859	20.35	44833.3362367940		44833.3358265972	13.95
173	08:04:10.918	20.43	44833.3362374768		44833.3358274884	15.28
174	08:04:10.972	18.92	44833.3362381019		44833.3358281713	20.59
175	08:04:11.015	18.98	44833.3362385995		44833.3358287500	20.10
176	08:04:11.057	20.56	44833.3362390856		44833.3358294792	20.75
177	08:04:11.109	19.85	44833.3362396875		44833.3358300810	21.50
Sheet1

Cell Formulas
Range	Formula
C3:C177	C3	=A3

 

[cmowla]

Hi.

How do you expect us to help you count the clusters and get the maximum speed in each cluster, if you haven't defined how to determine what a cluster is? You need to somehow mark the clusters in the same data that you have presented. Then we can provide formulas which counts how many of there are and also find the maximum speed in each one.

I was at first assuming that you would like it that every cluster is every 1.5 seconds, but you mentioned that there are "differing time intervals between each cluster", so that wouldn't help you.

EDIT:
If you did show us by that image, the resolution is too poor to see. Reattach a high quality image (or better yet, an xl2bb spreadsheet capture) of it.

 

[rdubs]

Hi there. Thanks for steering me.
Here are the first 10 clusters marked.
Depending on the speed of the object, the cluster cover 1 to 3 seconds. The gap between clusters could conceivably be less that this.

Speed clusters.xlsx
	A	B	C	D
2	time	speed	time as number
3	08:01:11.501	7.84	44833.3341608912	Cluster 1
4	08:01:11.550	8.08	44833.3341614583
5	08:01:11.606	14.28	44833.3341621065
6	08:01:11.649	17.67	44833.3341626042
7	08:01:11.699	7.48	44833.3341631829
8	08:01:11.792	9.55	44833.3341642593
9	08:01:11.862	8.72	44833.3341650694
10	08:01:11.917	14.68	44833.3341657060
11	08:01:11.961	11.84	44833.3341662153
12	08:01:12.015	17.96	44833.3341668403
13	08:01:12.071	17.88	44833.3341674884
14	08:01:12.114	17.49	44833.3341679861
15	08:01:12.156	18.81	44833.3341684722
16	08:01:12.196	21.36	44833.3341689352
17	08:01:12.238	14.21	44833.3341694213
18	08:01:12.280	19.54	44833.3341699074
19	08:01:12.333	19.93	44833.3341705208
20	08:01:12.385	19.61	44833.3341711227
21	08:01:12.439	19.71	44833.3341717477
22	08:01:12.578	19.97	44833.3341733565
23	08:01:12.676	16.08	44833.3341744907
24	08:01:12.723	21.13	44833.3341750347
25	08:01:12.786	15.74	44833.3341757639
26	08:01:12.830	20.70	44833.3341762731
27	08:01:35.986	10.02	44833.3344442824	Cluster 2
28	08:01:36.031	21.38	44833.3344448032
29	08:01:36.071	16.69	44833.3344452662
30	08:01:36.139	14.56	44833.3344460532
31	08:01:36.178	26.20	44833.3344465046
32	08:01:36.245	27.22	44833.3344472801
33	08:01:36.292	29.41	44833.3344478241
34	08:01:36.331	21.10	44833.3344482755
35	08:01:36.377	27.31	44833.3344488079
36	08:01:36.415	32.64	44833.3344492477
37	08:01:36.473	28.13	44833.3344499190
38	08:01:36.523	37.97	44833.3344504977
39	08:01:36.566	29.52	44833.3344509954
40	08:01:36.707	37.97	44833.3344526273
41	08:02:07.414	5.85	44833.3348080324	Cluster 3
42	08:02:07.461	13.73	44833.3348085764
43	08:02:07.509	10.66	44833.3348091319
44	08:02:07.594	12.22	44833.3348101157
45	08:02:07.642	15.21	44833.3348106713
46	08:02:07.691	13.56	44833.3348112384
47	08:02:07.769	8.21	44833.3348121412
48	08:02:07.918	7.05	44833.3348138657
49	08:02:07.965	8.71	44833.3348144097
50	08:02:08.025	10.69	44833.3348151042
51	08:02:08.103	16.17	44833.3348160069
52	08:02:08.145	13.23	44833.3348164931
53	08:02:08.197	16.61	44833.3348170949
54	08:02:08.239	16.47	44833.3348175810
55	08:02:08.301	15.96	44833.3348182986
56	08:02:08.345	16.01	44833.3348188079
57	08:02:08.402	18.23	44833.3348194676
58	08:02:08.455	18.61	44833.3348200810
59	08:02:08.517	18.27	44833.3348207986
60	08:02:08.558	18.74	44833.3348212732
61	08:02:08.600	18.85	44833.3348217593
62	08:02:08.648	19.30	44833.3348223148
63	08:02:08.690	18.89	44833.3348228009
64	08:02:08.731	19.51	44833.3348232755
65	08:02:08.774	19.47	44833.3348237731
66	08:02:08.816	19.37	44833.3348242593
67	08:02:08.874	19.85	44833.3348249306
68	08:02:08.918	19.93	44833.3348254398
69	08:02:09.030	20.51	44833.3348267361
70	08:02:09.071	20.13	44833.3348272107
71	08:02:09.126	20.25	44833.3348278472
72	08:02:09.199	12.35	44833.3348286921
73	08:02:52.964	19.80	44833.3353352315	Cluster 4
74	08:02:53.024	12.35	44833.3353359259
75	08:02:53.284	21.16	44833.3353389352
76	08:02:53.331	20.05	44833.3353394792
77	08:02:53.390	14.77	44833.3353401620
78	08:02:53.459	20.25	44833.3353409606
79	08:02:53.516	19.90	44833.3353416204
80	08:02:53.583	15.38	44833.3353423958
81	08:02:53.667	18.57	44833.3353433681
82	08:02:53.711	15.28	44833.3353438773
83	08:02:53.752	18.29	44833.3353443519
84	08:02:53.794	16.99	44833.3353448380
85	08:02:53.837	15.32	44833.3353453356
86	08:02:53.883	12.28	44833.3353458681
87	08:02:53.931	8.63	44833.3353464236
88	08:02:54.017	10.41	44833.3353474190
89	08:02:54.098	6.02	44833.3353483565
90	08:02:54.171	13.89	44833.3353492014
91	08:02:54.221	8.13	44833.3353497801
92	08:02:54.282	11.29	44833.3353504861
93	08:02:54.334	6.45	44833.3353510880
94	08:03:02.960	21.22	44833.3354509259	Cluster 5
95	08:03:03.181	20.70	44833.3354534838
96	08:03:03.235	20.97	44833.3354541088
97	08:03:03.276	21.02	44833.3354545833
98	08:03:03.321	20.30	44833.3354551042
99	08:03:03.378	20.35	44833.3354557639
100	08:03:03.434	20.41	44833.3354564120
101	08:03:03.476	19.75	44833.3354568981
102	08:03:03.520	17.63	44833.3354574074
103	08:03:03.564	14.26	44833.3354579167
104	08:03:03.621	16.03	44833.3354585764
105	08:03:03.663	15.96	44833.3354590625
106	08:03:03.717	15.61	44833.3354596875
107	08:03:03.774	16.01	44833.3354603472
108	08:03:03.819	10.60	44833.3354608681
109	08:03:03.861	12.99	44833.3354613542
110	08:03:03.908	9.34	44833.3354618981
111	08:03:03.965	6.54	44833.3354625579
112	08:03:04.028	17.38	44833.3354632870
113	08:03:04.085	19.23	44833.3354639468
114	08:03:04.131	12.75	44833.3354644792
115	08:03:04.177	13.95	44833.3354650116
116	08:03:04.221	13.62	44833.3354655208
117	08:03:04.275	6.83	44833.3354661458
118	08:03:12.922	5.85	44833.3355662269	Cluster 6
119	08:03:12.976	8.73	44833.3355668518
120	08:03:13.028	9.24	44833.3355674537
121	08:03:13.076	12.28	44833.3355680093
122	08:03:13.125	13.73	44833.3355685764
123	08:03:13.183	7.47	44833.3355692477
124	08:03:13.369	7.99	44833.3355714005
125	08:03:13.418	10.09	44833.3355719676
126	08:03:13.489	6.41	44833.3355727894
127	08:03:13.533	13.04	44833.3355732986
128	08:03:13.600	12.30	44833.3355740741
129	08:03:13.647	12.54	44833.3355746181
130	08:03:13.698	10.20	44833.3355752083
131	08:03:13.742	12.83	44833.3355757176
132	08:03:13.797	11.41	44833.3355763542
133	08:03:13.888	14.05	44833.3355774074
134	08:03:13.932	14.04	44833.3355779167
135	08:03:13.990	14.22	44833.3355785880
136	08:03:14.052	14.53	44833.3355793056
137	08:03:14.147	14.71	44833.3355804051
138	08:03:14.193	15.19	44833.3355809375
139	08:03:14.269	15.50	44833.3355818171
140	08:03:14.382	15.47	44833.3355831250
141	08:03:14.512	15.49	44833.3355846296
142	08:03:14.605	15.49	44833.3355857060
143	08:03:14.741	15.34	44833.3355872801
144	08:03:34.803	6.83	44833.3358194792	Cluster 7
145	08:03:34.860	10.14	44833.3358201389
146	08:03:34.921	12.46	44833.3358208449
147	08:03:34.989	7.39	44833.3358216319
148	08:03:35.037	8.43	44833.3358221875
149	08:03:35.139	7.44	44833.3358233681
150	08:03:35.186	8.91	44833.3358239120
151	08:03:35.231	10.54	44833.3358244329
152	08:03:35.291	15.43	44833.3358251273
153	08:03:35.334	13.75	44833.3358256250
154	08:03:35.375	15.78	44833.3358260995
155	08:03:35.418	13.95	44833.3358265972
156	08:03:35.495	15.28	44833.3358274884
157	08:03:35.554	20.59	44833.3358281713
158	08:03:35.604	20.10	44833.3358287500
159	08:03:35.667	20.75	44833.3358294792
160	08:03:35.719	21.50	44833.3358300810
161	08:03:35.797	21.56	44833.3358309838
162	08:03:35.842	21.73	44833.3358315046
163	08:03:35.889	21.67	44833.3358320486
164	08:03:36.007	22.34	44833.3358334144
165	08:03:36.047	21.97	44833.3358338773
166	08:03:36.120	22.56	44833.3358347222
167	08:03:36.227	22.56	44833.3358359606
168	08:04:10.650	23.48	44833.3362343750	Cluster 8
169	08:04:10.698	17.10	44833.3362349306
170	08:04:10.738	22.00	44833.3362353935
171	08:04:10.794	21.82	44833.3362360417
172	08:04:10.859	20.35	44833.3362367940
173	08:04:10.918	20.43	44833.3362374768
174	08:04:10.972	18.92	44833.3362381019
175	08:04:11.015	18.98	44833.3362385995
176	08:04:11.057	20.56	44833.3362390856
177	08:04:11.109	19.85	44833.3362396875
178	08:04:11.150	19.12	44833.3362401620
179	08:04:11.227	16.44	44833.3362410532
180	08:04:11.271	11.31	44833.3362415625
181	08:04:11.313	15.46	44833.3362420486
182	08:04:11.374	10.37	44833.3362427546
183	08:04:11.429	14.75	44833.3362433912
184	08:04:11.473	18.25	44833.3362439005
185	08:04:11.517	16.75	44833.3362444097
186	08:04:11.582	16.24	44833.3362451620
187	08:04:11.658	8.75	44833.3362460417
188	08:04:11.710	8.90	44833.3362466435
189	08:04:15.097	19.97	44833.3362858449	Cluster 9
190	08:04:15.194	15.08	44833.3362869676
191	08:04:15.305	19.93	44833.3362882523
192	08:04:15.457	19.75	44833.3362900116
193	08:04:15.580	20.23	44833.3362914352
194	08:04:15.621	18.92	44833.3362919097
195	08:04:15.674	19.40	44833.3362925231
196	08:04:15.729	19.26	44833.3362931597
197	08:04:15.777	17.63	44833.3362937153
198	08:04:15.819	17.49	44833.3362942014
199	08:04:15.866	16.59	44833.3362947454
200	08:04:15.908	16.37	44833.3362952315
201	08:04:15.970	12.24	44833.3362959491
202	08:04:16.017	13.68	44833.3362964931
203	08:04:16.064	13.63	44833.3362970370
204	08:04:16.112	12.23	44833.3362975926
205	08:04:16.176	13.20	44833.3362983333
206	08:04:16.228	7.72	44833.3362989352
207	08:04:16.295	6.19	44833.3362997106
208	08:04:16.368	9.42	44833.3363005556
209	08:04:16.449	9.40	44833.3363014931
210	08:04:16.497	9.94	44833.3363020486
211	08:04:16.546	11.18	44833.3363026157
212	08:04:59.063	7.63	44833.3367947106	Cluster 10
Sheet1

Cell Formulas
Range	Formula
C3:C212	C3	=A3

 

[rdubs]

Hi.

How do you expect us to help you count the clusters and get the maximum speed in each cluster, if you haven't defined how to determine what a cluster is? You need to somehow mark the clusters in the same data that you have presented. Then we can provide formulas which counts how many of there are and also find the maximum speed in each one.

I was at first assuming that you would like it that every cluster is every 1.5 seconds, but you mentioned that there are "differing time intervals between each cluster", so that wouldn't help you.

EDIT:
If you did show us by that image, the resolution is too poor to see. Reattach a high quality image (or better yet, an xl2bb spreadsheet capture) of it.

Thanks for helping.
Here is a higher res image, and my previous reply has the clusters in the xl2bb

 

[gifariz]

Thanks for helping.
Here is a higher res image, and my previous reply has the clusters in the xl2bb

Please see my image attached. Sorry I am not familiar with XL2BB yet.
Formula for cells in yellow are in row 2.

If we sort the time gap, we can notice what is the appropriate boundary value of time gap for clustering.

 

[cmowla]

How about this?

Book1.xlsx
	A	B	C	D	E	F	G	H
3	time	speed	time as number			Number of Clusters:	Max Speed:
4	08:01:11.501	7.841705		Cluster 1		10
5	08:01:11.550	8.078377				Cluster 1	21.35613401	4
6	08:01:11.606	14.27539				Cluster 2	37.96646046	28
7	08:01:11.649	17.66756				Cluster 3	20.51061657	42
8	08:01:11.699	7.476635				Cluster 4	21.15917361	74
9	08:01:11.792	9.548046				Cluster 5	21.21507632	95
10	08:01:11.862	8.718682				Cluster 6	15.50174978	119
11	08:01:11.917	14.6799				Cluster 7	22.55591682	145
12	08:01:11.961	11.84352				Cluster 8	23.47925844	169
13	08:01:12.015	17.964				Cluster 9	20.22646445	190
14	08:01:12.071	17.88398				Cluster 10	?	213
15	08:01:12.114	17.49435
16	08:01:12.156	18.8054
17	08:01:12.196	21.35613
18	08:01:12.238	14.21222
19	08:01:12.280	19.53749
20	08:01:12.333	19.92533
21	08:01:12.385	19.60905
22	08:01:12.439	19.70529
23	08:01:12.578	19.97489
24	08:01:12.676	16.07589
25	08:01:12.723	21.13133
26	08:01:12.786	15.74491
27	08:01:12.830	20.69564
28	08:01:35.986	10.02485		Cluster 2
29	08:01:36.031	21.38457
30	08:01:36.071	16.69419
31	08:01:36.139	14.56012
32	08:01:36.178	26.19872
33	08:01:36.245	27.22002
34	08:01:36.292	29.41358
35	08:01:36.331	21.10356
36	08:01:36.377	27.31261
37	08:01:36.415	32.6419
38	08:01:36.473	28.12577
39	08:01:36.523	37.96646
40	08:01:36.566	29.52171
41	08:01:36.707	37.96646
42	08:02:07.414	5.854835		Cluster 3
43	08:02:07.461	13.72634
44	08:02:07.509	10.66389
45	08:02:07.594	12.22208
46	08:02:07.642	15.20816
47	08:02:07.691	13.56403
48	08:02:07.769	8.214738
49	08:02:07.918	7.053058
50	08:02:07.965	8.713952
51	08:02:08.025	10.68517
52	08:02:08.103	16.17302
53	08:02:08.145	13.22884
54	08:02:08.197	16.60787
55	08:02:08.239	16.4716
56	08:02:08.301	15.96403
57	08:02:08.345	16.01178
58	08:02:08.402	18.22907
59	08:02:08.455	18.60928
60	08:02:08.517	18.27055
61	08:02:08.558	18.73957
62	08:02:08.600	18.84955
63	08:02:08.648	19.30266
64	08:02:08.690	18.8939
65	08:02:08.731	19.51375
66	08:02:08.774	19.46644
67	08:02:08.816	19.37251
68	08:02:08.874	19.85144
69	08:02:08.918	19.92533
70	08:02:09.030	20.51062
71	08:02:09.071	20.12508
72	08:02:09.126	20.25197
73	08:02:09.199	12.3537
74	08:02:52.964	19.80248		Cluster 4
75	08:02:53.024	12.3537
76	08:02:53.284	21.15917
77	08:02:53.331	20.0497
78	08:02:53.390	14.77444
79	08:02:53.459	20.25197
80	08:02:53.516	19.90064
81	08:02:53.583	15.38296
82	08:02:53.667	18.56626
83	08:02:53.711	15.28051
84	08:02:53.752	18.29136
85	08:02:53.794	16.99451
86	08:02:53.837	15.32425
87	08:02:53.883	12.27814
88	08:02:53.931	8.625034
89	08:02:54.017	10.41492
90	08:02:54.098	6.023936
91	08:02:54.171	13.89257
92	08:02:54.221	8.127436
93	08:02:54.282	11.29382
94	08:02:54.334	6.447135
95	08:03:02.960	21.21508		Cluster 5
96	08:03:03.181	20.69564
97	08:03:03.235	20.96581
98	08:03:03.276	21.0207
99	08:03:03.321	20.30318
100	08:03:03.378	20.35464
101	08:03:03.434	20.40637
102	08:03:03.476	19.75377
103	08:03:03.520	17.62877
104	08:03:03.564	14.26271
105	08:03:03.621	16.02776
106	08:03:03.663	15.96403
107	08:03:03.717	15.6072
108	08:03:03.774	16.01178
109	08:03:03.819	10.60054
110	08:03:03.861	12.99338
111	08:03:03.908	9.3371
112	08:03:03.965	6.544341
113	08:03:04.028	17.38075
114	08:03:04.085	19.23331
115	08:03:04.131	12.74588
116	08:03:04.177	13.95292
117	08:03:04.221	13.62155
118	08:03:04.275	6.828152
119	08:03:12.922	5.848439		Cluster 6
120	08:03:12.976	8.728159
121	08:03:13.028	9.240399
122	08:03:13.076	12.27814
123	08:03:13.125	13.72634
124	08:03:13.183	7.46968
125	08:03:13.369	7.993934
126	08:03:13.418	10.08782
127	08:03:13.489	6.411103
128	08:03:13.533	13.03556
129	08:03:13.600	12.29695
130	08:03:13.647	12.53693
131	08:03:13.698	10.20318
132	08:03:13.742	12.82733
133	08:03:13.797	11.40612
134	08:03:13.888	14.05058
135	08:03:13.932	14.0383
136	08:03:13.990	14.22481
137	08:03:14.052	14.53377
138	08:03:14.147	14.70679
139	08:03:14.193	15.19377
140	08:03:14.269	15.50175
141	08:03:14.382	15.47188
142	08:03:14.512	15.4868
143	08:03:14.605	15.4868
144	08:03:14.741	15.33889
145	08:03:34.803	6.82525		Cluster 7
146	08:03:34.860	10.13877
147	08:03:34.921	12.45913
148	08:03:34.989	7.394021
149	08:03:35.037	8.425925
150	08:03:35.139	7.435099
151	08:03:35.186	8.907273
152	08:03:35.231	10.54485
153	08:03:35.291	15.42729
154	08:03:35.334	13.74984
155	08:03:35.375	15.77585
156	08:03:35.418	13.95292
157	08:03:35.495	15.28051
158	08:03:35.554	20.5895
159	08:03:35.604	20.09989
160	08:03:35.667	20.74911
161	08:03:35.719	21.49908
162	08:03:35.797	21.5568
163	08:03:35.842	21.73182
164	08:03:35.889	21.67316
165	08:03:36.007	22.33632
166	08:03:36.047	21.96965
167	08:03:36.120	22.55592
168	08:03:36.227	22.55592
169	08:04:10.650	23.47926		Cluster 8
170	08:04:10.698	17.1031
171	08:04:10.738	21.99974
172	08:04:10.794	21.8204
173	08:04:10.859	20.35464
174	08:04:10.918	20.43233
175	08:04:10.972	18.91615
176	08:04:11.015	18.98323
177	08:04:11.057	20.56314
178	44833.33624	19.85144
179	44833.33624	19.11882
180	44833.33624	16.43788
181	44833.33624	11.30973
182	44833.33624	15.45699
183	44833.33624	10.37456
184	44833.33624	14.7473
185	44833.33624	18.24979
186	44833.33624	16.74642
187	44833.33625	16.23844
188	44833.33625	8.747175
189	44833.33625	8.902335
190	44833.33629	19.97489		Cluster 9
191	44833.33629	15.07964
192	44833.33629	19.92533
193	44833.33629	19.75377
194	44833.33629	20.22646
195	44833.33629	18.91615
196	44833.33629	19.39591
197	44833.33629	19.25637
198	44833.33629	17.62877
199	44833.33629	17.49435
200	44833.33629	16.59072
201	44833.3363	16.37086
202	44833.3363	12.24071
203	44833.3363	13.67957
204	44833.3363	13.63312
205	44833.3363	12.23139
206	44833.3363	13.19623
207	44833.3363	7.721064
208	44833.3363	6.188753
209	44833.3363	9.424773
210	44833.3363	9.397199
211	44833.3363	9.944157
212	44833.3363	11.18371
213	44833.33679	7.625742		Cluster 10
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
Sheet6

Cell Formulas
Range	Formula
F4	F4	=ROWS(FILTER(IF(COUNT(FIND("Cluster",D:D))>0,ROW(D:D),""),IF(COUNT(FIND("Cluster",D:D))>0,D:D,"")<>""))
F5:F14	F5	="Cluster "&ROW()-4
G5:G14	G5	=IFERROR(MAX(INDEX(B:B,SEQUENCE(H6-H5,,H5))),"?")
H5:H14	H5	=FILTER(IF(COUNT(FIND("Cluster",D:D))>0,ROW(D:D),""),IF(COUNT(FIND("Cluster",D:D))>0,D:D,"")<>"")
Dynamic array formulas.

 

[rdubs]

Please see my image attached. Sorry I am not familiar with XL2BB yet.
Formula for cells in yellow are in row 2.

If we sort the time gap, we can notice what is the appropriate boundary value of time gap for clustering.

This is amazing - thanks so much. I'll take time to understand how it works.
I tried to have Time gap sorted (F) and Cluster (H) without the range, just the whole column eg C:C. Is that possible?

 

[rdubs]

How about this?

Book1.xlsx
	A	B	C	D	E	F	G	H
3	time	speed	time as number			Number of Clusters:	Max Speed:
4	08:01:11.501	7.841705		Cluster 1		10
5	08:01:11.550	8.078377				Cluster 1	21.35613401	4
6	08:01:11.606	14.27539				Cluster 2	37.96646046	28
7	08:01:11.649	17.66756				Cluster 3	20.51061657	42
8	08:01:11.699	7.476635				Cluster 4	21.15917361	74
9	08:01:11.792	9.548046				Cluster 5	21.21507632	95
10	08:01:11.862	8.718682				Cluster 6	15.50174978	119
11	08:01:11.917	14.6799				Cluster 7	22.55591682	145
12	08:01:11.961	11.84352				Cluster 8	23.47925844	169
13	08:01:12.015	17.964				Cluster 9	20.22646445	190
14	08:01:12.071	17.88398				Cluster 10	?	213
15	08:01:12.114	17.49435
16	08:01:12.156	18.8054
17	08:01:12.196	21.35613
18	08:01:12.238	14.21222
19	08:01:12.280	19.53749
20	08:01:12.333	19.92533
21	08:01:12.385	19.60905
22	08:01:12.439	19.70529
23	08:01:12.578	19.97489
24	08:01:12.676	16.07589
25	08:01:12.723	21.13133
26	08:01:12.786	15.74491
27	08:01:12.830	20.69564
28	08:01:35.986	10.02485		Cluster 2
29	08:01:36.031	21.38457
30	08:01:36.071	16.69419
31	08:01:36.139	14.56012
32	08:01:36.178	26.19872
33	08:01:36.245	27.22002
34	08:01:36.292	29.41358
35	08:01:36.331	21.10356
36	08:01:36.377	27.31261
37	08:01:36.415	32.6419
38	08:01:36.473	28.12577
39	08:01:36.523	37.96646
40	08:01:36.566	29.52171
41	08:01:36.707	37.96646
42	08:02:07.414	5.854835		Cluster 3
43	08:02:07.461	13.72634
44	08:02:07.509	10.66389
45	08:02:07.594	12.22208
46	08:02:07.642	15.20816
47	08:02:07.691	13.56403
48	08:02:07.769	8.214738
49	08:02:07.918	7.053058
50	08:02:07.965	8.713952
51	08:02:08.025	10.68517
52	08:02:08.103	16.17302
53	08:02:08.145	13.22884
54	08:02:08.197	16.60787
55	08:02:08.239	16.4716
56	08:02:08.301	15.96403
57	08:02:08.345	16.01178
58	08:02:08.402	18.22907
59	08:02:08.455	18.60928
60	08:02:08.517	18.27055
61	08:02:08.558	18.73957
62	08:02:08.600	18.84955
63	08:02:08.648	19.30266
64	08:02:08.690	18.8939
65	08:02:08.731	19.51375
66	08:02:08.774	19.46644
67	08:02:08.816	19.37251
68	08:02:08.874	19.85144
69	08:02:08.918	19.92533
70	08:02:09.030	20.51062
71	08:02:09.071	20.12508
72	08:02:09.126	20.25197
73	08:02:09.199	12.3537
74	08:02:52.964	19.80248		Cluster 4
75	08:02:53.024	12.3537
76	08:02:53.284	21.15917
77	08:02:53.331	20.0497
78	08:02:53.390	14.77444
79	08:02:53.459	20.25197
80	08:02:53.516	19.90064
81	08:02:53.583	15.38296
82	08:02:53.667	18.56626
83	08:02:53.711	15.28051
84	08:02:53.752	18.29136
85	08:02:53.794	16.99451
86	08:02:53.837	15.32425
87	08:02:53.883	12.27814
88	08:02:53.931	8.625034
89	08:02:54.017	10.41492
90	08:02:54.098	6.023936
91	08:02:54.171	13.89257
92	08:02:54.221	8.127436
93	08:02:54.282	11.29382
94	08:02:54.334	6.447135
95	08:03:02.960	21.21508		Cluster 5
96	08:03:03.181	20.69564
97	08:03:03.235	20.96581
98	08:03:03.276	21.0207
99	08:03:03.321	20.30318
100	08:03:03.378	20.35464
101	08:03:03.434	20.40637
102	08:03:03.476	19.75377
103	08:03:03.520	17.62877
104	08:03:03.564	14.26271
105	08:03:03.621	16.02776
106	08:03:03.663	15.96403
107	08:03:03.717	15.6072
108	08:03:03.774	16.01178
109	08:03:03.819	10.60054
110	08:03:03.861	12.99338
111	08:03:03.908	9.3371
112	08:03:03.965	6.544341
113	08:03:04.028	17.38075
114	08:03:04.085	19.23331
115	08:03:04.131	12.74588
116	08:03:04.177	13.95292
117	08:03:04.221	13.62155
118	08:03:04.275	6.828152
119	08:03:12.922	5.848439		Cluster 6
120	08:03:12.976	8.728159
121	08:03:13.028	9.240399
122	08:03:13.076	12.27814
123	08:03:13.125	13.72634
124	08:03:13.183	7.46968
125	08:03:13.369	7.993934
126	08:03:13.418	10.08782
127	08:03:13.489	6.411103
128	08:03:13.533	13.03556
129	08:03:13.600	12.29695
130	08:03:13.647	12.53693
131	08:03:13.698	10.20318
132	08:03:13.742	12.82733
133	08:03:13.797	11.40612
134	08:03:13.888	14.05058
135	08:03:13.932	14.0383
136	08:03:13.990	14.22481
137	08:03:14.052	14.53377
138	08:03:14.147	14.70679
139	08:03:14.193	15.19377
140	08:03:14.269	15.50175
141	08:03:14.382	15.47188
142	08:03:14.512	15.4868
143	08:03:14.605	15.4868
144	08:03:14.741	15.33889
145	08:03:34.803	6.82525		Cluster 7
146	08:03:34.860	10.13877
147	08:03:34.921	12.45913
148	08:03:34.989	7.394021
149	08:03:35.037	8.425925
150	08:03:35.139	7.435099
151	08:03:35.186	8.907273
152	08:03:35.231	10.54485
153	08:03:35.291	15.42729
154	08:03:35.334	13.74984
155	08:03:35.375	15.77585
156	08:03:35.418	13.95292
157	08:03:35.495	15.28051
158	08:03:35.554	20.5895
159	08:03:35.604	20.09989
160	08:03:35.667	20.74911
161	08:03:35.719	21.49908
162	08:03:35.797	21.5568
163	08:03:35.842	21.73182
164	08:03:35.889	21.67316
165	08:03:36.007	22.33632
166	08:03:36.047	21.96965
167	08:03:36.120	22.55592
168	08:03:36.227	22.55592
169	08:04:10.650	23.47926		Cluster 8
170	08:04:10.698	17.1031
171	08:04:10.738	21.99974
172	08:04:10.794	21.8204
173	08:04:10.859	20.35464
174	08:04:10.918	20.43233
175	08:04:10.972	18.91615
176	08:04:11.015	18.98323
177	08:04:11.057	20.56314
178	44833.33624	19.85144
179	44833.33624	19.11882
180	44833.33624	16.43788
181	44833.33624	11.30973
182	44833.33624	15.45699
183	44833.33624	10.37456
184	44833.33624	14.7473
185	44833.33624	18.24979
186	44833.33624	16.74642
187	44833.33625	16.23844
188	44833.33625	8.747175
189	44833.33625	8.902335
190	44833.33629	19.97489		Cluster 9
191	44833.33629	15.07964
192	44833.33629	19.92533
193	44833.33629	19.75377
194	44833.33629	20.22646
195	44833.33629	18.91615
196	44833.33629	19.39591
197	44833.33629	19.25637
198	44833.33629	17.62877
199	44833.33629	17.49435
200	44833.33629	16.59072
201	44833.3363	16.37086
202	44833.3363	12.24071
203	44833.3363	13.67957
204	44833.3363	13.63312
205	44833.3363	12.23139
206	44833.3363	13.19623
207	44833.3363	7.721064
208	44833.3363	6.188753
209	44833.3363	9.424773
210	44833.3363	9.397199
211	44833.3363	9.944157
212	44833.3363	11.18371
213	44833.33679	7.625742		Cluster 10
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
Sheet6

Cell Formulas
Range	Formula
F4	F4	=ROWS(FILTER(IF(COUNT(FIND("Cluster",D:D))>0,ROW(D:D),""),IF(COUNT(FIND("Cluster",D:D))>0,D:D,"")<>""))
F5:F14	F5	="Cluster "&ROW()-4
G5:G14	G5	=IFERROR(MAX(INDEX(B:B,SEQUENCE(H6-H5,,H5))),"?")
H5:H14	H5	=FILTER(IF(COUNT(FIND("Cluster",D:D))>0,ROW(D:D),""),IF(COUNT(FIND("Cluster",D:D))>0,D:D,"")<>"")
Dynamic array formulas.

Thank you so much.

Please can you confirm if this relies on the clusters being pre-defined? I'm hoping the formula can create the clusters, which gifariz's solution does.

 

[cmowla]

Please can you confirm if this relies on the clusters being pre-defined? I'm hoping the formula can create the clusters, which gifariz's solution does.

Yes mine does, but the maximum speed that I got and he got are different for like, the 7th cluster (and several others). So his formula doesn't work for how you defined the clusters. How can it? The rows associated with each cluster are random. Even if me manages to create a formula/function which fits to this specific set of clusters with regression, what good will it do with a new set of data and/or more cluster trials added to this data set?

Again, we cannot know what rows a cluster occupies. It's random! You have to define/mark them yourself somehow.

 

[rdubs]

Yes mine does, but the maximum speed that I got and he got are different for like, the 7th cluster (and several others). So his formula doesn't work for how you defined the clusters. How can it? The rows associated with each cluster are random. Even if me manages to create a formula/function which fits to this specific set of clusters with regression, what good will it do with a new set of data and/or more cluster trials added to this data set?

Again, we cannot know what rows a cluster occupies. It's random! You have to define/mark them yourself somehow.

Thanks cmowla.
I adjusted the gap to 3000 milliseconds and the 2 solutions here now have the same max speeds for each cluster.

I realise that it's not possible to be 100% accurate, but the clustering needs to be 'good enough', which is now most certainly is.

I'm in a good place with this now - thanks to you both.