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

 

[rdubs]

Hi @cmowla and @gifariz
I hope you could help further with 2 further functions I'd like to to achieve, 1 of which I've made progress on but not solved it yet.

1. Find the corresponding time to the highest speed in a cluster. I made some progress with Offset Index Match, but this returns the time of the first occurrence of the max speed. In my sample data here, Vcluster 6 shows the time where speed 23.04 appeared first in the Speed column (F19), rather than the corresponding time at E178

2. Very occasionally there are anomalous reading logged in the data, eg Vcluster 4 shows 65.21 (F157) which is way above the other speed logs in that Vcluster. I need a function which filters outliers like these where the value varies greatly from the average within the cluster (24.91 in this case), or its 'gap' from the others is significantly more.

Any pointers very gratefully received.

Vcluster	ClusterMaxTime	ClusterMaxSp	unixepoch	hh:mm:ss.000	Speed	Gap	Cluster
0	15:40:54.495	33.46	1665240052909	15:40:52.909	7.17
0	15:40:54.495	33.46	1665240053225	15:40:53.225	21.56	316	0
1	15:40:58.683	27.41	1665240053288	15:40:53.288	24.44	63	0
2	15:41:23.933	41.93	1665240053762	15:40:53.762	11.37	474	0
3	15:41:31.827	44.36	1665240053831	15:40:53.831	18.25	69	0
4	15:41:38.473	65.21	1665240053887	15:40:53.887	6.74	56	0
5	15:41:58.059	48.67	1665240054009	15:40:54.009	12.46	122	0
6	15:40:54.753	23.04	1665240054057	15:40:54.057	17.19	48	0
			1665240054095	15:40:54.095	24.48	38	0
			1665240054135	15:40:54.135	26.50	40	0
			1665240054249	15:40:54.249	29.20	114	0
			1665240054306	15:40:54.306	30.30	57	0
			1665240054495	15:40:54.495	33.46	189	0
			1665240054561	15:40:54.561	32.06	66	0
			1665240054595	15:40:54.595	33.25	34	0
			1665240054660	15:40:54.660	25.21	65	0
			1665240054705	15:40:54.705	33.32	45	0
			1665240054753	15:40:54.753	23.04	48	0
			1665240054843	15:40:54.843	33.18	90	0
			1665240058032	15:40:58.032	5.82	3189	1
			1665240058071	15:40:58.071	16.44	39	1
			1665240058124	15:40:58.124	13.39	53	1
			1665240058189	15:40:58.189	9.24	65	1
			1665240058244	15:40:58.244	5.99	55	1
			1665240058290	15:40:58.290	9.22	46	1
			1665240058345	15:40:58.345	25.86	55	1
			1665240058394	15:40:58.394	19.49	49	1
			1665240058433	15:40:58.433	16.98	39	1
			1665240058482	15:40:58.482	12.65	49	1
			1665240058562	15:40:58.562	24.63	80	1
			1665240058615	15:40:58.615	19.85	53	1
			1665240058683	15:40:58.683	27.41	68	1
			1665240058821	15:40:58.821	20.15	138	1
			1665240070384	15:41:10.384	10.50	11563	2
			1665240070424	15:41:10.424	15.73	40	2
			1665240070465	15:41:10.465	24.82	41	2
			1665240070502	15:41:10.502	29.63	37	2
			1665240070581	15:41:10.581	36.92	79	2
			1665240070649	15:41:10.649	12.16	68	2
			1665240070733	15:41:10.733	26.28	84	2
			1665240070777	15:41:10.777	37.97	44	2
			1665240070862	15:41:10.862	37.26	85	2
			1665240070928	15:41:10.928	36.83	66	2
			1665240072341	15:41:12.341	17.96	1413	2
			1665240072421	15:41:12.421	11.88	80	2
			1665240072474	15:41:12.474	7.63	53	2
			1665240072538	15:41:12.538	19.33	64	2
			1665240072578	15:41:12.578	27.36	40	2
			1665240072709	15:41:12.709	15.64	131	2
			1665240072750	15:41:12.750	18.72	41	2
			1665240072798	15:41:12.798	21.24	48	2
			1665240072846	15:41:12.846	23.76	48	2
			1665240072884	15:41:12.884	29.58	38	2
			1665240072924	15:41:12.924	22.43	40	2
			1665240072961	15:41:12.961	24.75	37	2
			1665240073009	15:41:13.009	34.39	48	2
			1665240073050	15:41:13.050	36.17	41	2
			1665240073144	15:41:13.144	34.46	94	2
			1665240073640	15:41:13.640	26.28	496	2
			1665240075042	15:41:15.042	9.30	1402	2
			1665240075081	15:41:15.081	16.85	39	2
			1665240075186	15:41:15.186	15.19	105	2
			1665240075243	15:41:15.243	16.85	57	2
			1665240075283	15:41:15.283	19.23	40	2
			1665240075326	15:41:15.326	27.17	43	2
			1665240075368	15:41:15.368	24.33	42	2
			1665240075417	15:41:15.417	35.37	49	2
			1665240075507	15:41:15.507	35.69	90	2
			1665240075568	15:41:15.568	37.79	61	2
			1665240075719	15:41:15.719	29.04	151	2
			1665240078624	15:41:18.624	6.65	2905	2
			1665240078667	15:41:18.667	12.02	43	2
			1665240078804	15:41:18.804	9.54	137	2
			1665240078843	15:41:18.843	22.27	39	2
			1665240078980	15:41:18.980	29.52	137	2
			1665240079114	15:41:19.114	36.92	134	2
			1665240079219	15:41:19.219	37.79	105	2
			1665240079385	15:41:19.385	38.70	166	2
			1665240079442	15:41:19.442	38.51	57	2
			1665240079501	15:41:19.501	39.56	59	2
			1665240080812	15:41:20.812	17.14	1311	2
			1665240080956	15:41:20.956	26.33	144	2
			1665240081025	15:41:21.025	17.16	69	2
			1665240081090	15:41:21.090	11.08	65	2
			1665240081252	15:41:21.252	15.73	162	2
			1665240081306	15:41:21.306	14.31	54	2
			1665240081363	15:41:21.363	25.37	57	2
			1665240081454	15:41:21.454	6.02	91	2
			1665240081501	15:41:21.501	8.34	47	2
			1665240081555	15:41:21.555	25.25	54	2
			1665240081595	15:41:21.595	32.71	40	2
			1665240081635	15:41:21.635	30.94	40	2
			1665240081683	15:41:21.683	33.32	48	2
			1665240081731	15:41:21.731	35.69	48	2
			1665240081803	15:41:21.803	35.45	72	2
			1665240082012	15:41:22.012	17.46	209	2
			1665240082049	15:41:22.049	39.27	37	2
			1665240082823	15:41:22.823	6.80	774	2
			1665240082915	15:41:22.915	8.51	92	2
			1665240082958	15:41:22.958	11.22	43	2
			1665240082999	15:41:22.999	24.90	41	2
			1665240083047	15:41:23.047	21.05	48	2
			1665240083095	15:41:23.095	14.34	48	2
			1665240083136	15:41:23.136	13.37	41	2
			1665240083175	15:41:23.175	20.54	39	2
			1665240083213	15:41:23.213	28.47	38	2
			1665240083261	15:41:23.261	30.71	48	2
			1665240083301	15:41:23.301	23.24	40	2
			1665240083351	15:41:23.351	38.89	50	2
			1665240083417	15:41:23.417	37.26	66	2
			1665240083534	15:41:23.534	24.33	117	2
			1665240083664	15:41:23.664	40.25	130	2
			1665240083811	15:41:23.811	41.50	147	2
			1665240083872	15:41:23.872	40.86	61	2
			1665240083933	15:41:23.933	41.93	61	2
			1665240083986	15:41:23.986	40.76	53	2
			1665240084943	15:41:24.943	15.17	957	2
			1665240085006	15:41:25.006	24.71	63	2
			1665240085067	15:41:25.067	12.84	61	2
			1665240085115	15:41:25.115	28.83	48	2
			1665240085190	15:41:25.190	30.13	75	2
			1665240085228	15:41:25.228	18.38	38	2
			1665240085292	15:41:25.292	11.36	64	2
			1665240091368	15:41:31.368	21.73	6076	3
			1665240091408	15:41:31.408	16.98	40	3
			1665240091476	15:41:31.476	31.43	68	3
			1665240091781	15:41:31.781	41.82	305	3
			1665240091827	15:41:31.827	44.36	46	3
			1665240096238	15:41:36.238	10.24	4411	4
			1665240096280	15:41:36.280	18.31	42	4
			1665240096422	15:41:36.422	15.17	142	4
			1665240096464	15:41:36.464	16.29	42	4
			1665240096511	15:41:36.511	20.62	47	4
			1665240096561	15:41:36.561	25.41	50	4
			1665240096614	15:41:36.614	31.61	53	4
			1665240096683	15:41:36.683	34.54	69	4
			1665240096763	15:41:36.763	35.07	80	4
			1665240096798	15:41:36.798	34.99	35	4
			1665240097701	15:41:37.701	13.98	903	4
			1665240097762	15:41:37.762	8.25	61	4
			1665240097801	15:41:37.801	15.78	39	4
			1665240097840	15:41:37.840	22.88	39	4
			1665240097919	15:41:37.919	10.52	79	4
			1665240097965	15:41:37.965	9.47	46	4
			1665240098006	15:41:38.006	21.30	41	4
			1665240098044	15:41:38.044	27.22	38	4
			1665240098082	15:41:38.082	30.36	38	4
			1665240098118	15:41:38.118	27.59	36	4
			1665240098156	15:41:38.156	33.46	38	4
			1665240098198	15:41:38.198	33.88	42	4
			1665240098251	15:41:38.251	33.25	53	4
			1665240098298	15:41:38.298	36.50	47	4
			1665240098332	15:41:38.332	36.67	34	4
			1665240098397	15:41:38.397	37.35	65	4
			1665240098433	15:41:38.433	37.97	36	4
			1665240098473	15:41:38.473	65.21	40	4
			1665240098544	15:41:38.544	37.00	71	4
			1665240098630	15:41:38.630	37.35	86	4
			1665240101269	15:41:41.269	16.98	2639	4
			1665240101323	15:41:41.323	5.87	54	4
			1665240101370	15:41:41.370	8.76	47	4
			1665240101418	15:41:41.418	31.49	48	4
			1665240101461	15:41:41.461	22.78	43	4
			1665240101534	15:41:41.534	14.46	73	4
			1665240101572	15:41:41.572	18.83	38	4
			1665240102314	15:41:42.314	27.17	742	4
			1665240104657	15:41:44.657	6.99	2343	4
			1665240117815	15:41:57.815	9.77	13158	5
			1665240117882	15:41:57.882	31.61	67	5
			1665240118059	15:41:58.059	48.67	177	5
			1665240124065	15:42:04.065	11.90	6006	6
			1665240124119	15:42:04.119	15.85	54	6
			1665240124207	15:42:04.207	10.69	88	6
			1665240124251	15:42:04.251	11.91	44	6
			1665240124308	15:42:04.308	20.10	57	6
			1665240124370	15:42:04.370	9.82	62	6
			1665240124419	15:42:04.419	23.04	49	6
			1665240126496	15:42:06.496	8.16	2077	6
			1665240126555	15:42:06.555	5.96	59	6
			1665240126647	15:42:06.647	16.61	92	6
			1665240126722	15:42:06.722	23.04	75	6

 

[rdubs]

I've now solved point 1 with a slightly more crude way by simply taking the max time in a vCluster.

 

[gifariz]

Hi @cmowla and @gifariz
I hope you could help further with 2 further functions I'd like to to achieve, 1 of which I've made progress on but not solved it yet.

1. Find the corresponding time to the highest speed in a cluster. I made some progress with Offset Index Match, but this returns the time of the first occurrence of the max speed. In my sample data here, Vcluster 6 shows the time where speed 23.04 appeared first in the Speed column (F19), rather than the corresponding time at E178

2. Very occasionally there are anomalous reading logged in the data, eg Vcluster 4 shows 65.21 (F157) which is way above the other speed logs in that Vcluster. I need a function which filters outliers like these where the value varies greatly from the average within the cluster (24.91 in this case), or its 'gap' from the others is significantly more.

Any pointers very gratefully received.

1. I'm not sure why you take max time in a cluster. I think corresponding time can be obtained by making a helper column that contains both cluster & speed data.
For example in cell I2, =H2&"_"&F2, and so on for all data. Then index match to this helper column, B2=INDEX(E:E,MATCH(A2&"_"&C2,I:I,0)).

2. This is tricky because again the outlier limit is not well defined. One that I can think of is to compare each speed relatively with average (or with standard deviation also) of speed in that cluster. And filter the data within acceptable ratio. For example set Limit=2. Then FilteredSpeed = IF(AND(Speed/ClusterAvgSpeed>(1/Limit),Speed/ClusterAvgSpeed<Limit),Speed,NA()). Then use this filtered speed column as basic data for further data processing. Hope you understand as I haven't had time to make the excel.

 

[rdubs]

1. I'm not sure why you take max time in a cluster. I think corresponding time can be obtained by making a helper column that contains both cluster & speed data.
For example in cell I2, =H2&"_"&F2, and so on for all data. Then index match to this helper column, B2=INDEX(E:E,MATCH(A2&"_"&C2,I:I,0)).

2. This is tricky because again the outlier limit is not well defined. One that I can think of is to compare each speed relatively with average (or with standard deviation also) of speed in that cluster. And filter the data within acceptable ratio. For example set Limit=2. Then FilteredSpeed = IF(AND(Speed/ClusterAvgSpeed>(1/Limit),Speed/ClusterAvgSpeed<Limit),Speed,NA()). Then use this filtered speed column as basic data for further data processing. Hope you understand as I haven't had time to make the excel.

This is great, thank you. Item 1 makes perfect sense. I will explore your suggestion for item 2.

I really appreciate your expertise