Complicated Excel document using thousands of data points a day

**ACrossley1** · 01-31-2014, 04:50 PM

Hi,

Everyday I export an excel file and it comes out with thousands and thousands of data points. It is from the equipment I use. From when I turn the equipment to when I turn it off, it takes 5 measurements a second. So this is why I have thousands and thousands of data points.

In one column I have the time, the second column I have the current speed at that precise time, and in the third column I have the distance.

What i want to work out is if I pick a certain speed the equipment is at, I can then (from the formatting) work at how many efforts at that speed, the time spent at that speed and how much distance has been covered at that speed also.

For it too be classed as a certain a speed, there has to be a minimum of 5 data points in a row of that same speed. How can I on excel, work out this?

I hope this makes sense.

**bryan0** · 01-31-2014, 05:03 PM

Try posting a small subset of your data, and what you want to see.

It sounds like using a SUMIF and COUNTIF function could work. You would need to sum the distance column where the speed equals a certain value, and count the number of occurances?

**ACrossley1** · 01-31-2014, 05:52 PM

Yes that sounds perfect. so how long it spends at that speed, how many efforts at that speed, distance completed at that speed etc (an effort or the speed is only class at being in that threshold when it lasts for 5 data points).

but that sounds perfect.

 GPS Time	 Vel(Dpr)	 Odometer
 11:58:53	0	5208
 11:58:53	0	5208
 11:58:53	0	5208
 11:58:53	0	5208
 11:58:54	0	5208
 11:58:54	0	5208
 11:58:54	0	5208
 11:58:54	0	5208
 11:58:54	0	5208
 11:58:55	0	5208
 11:58:55	0	5208
 11:58:55	0	5208
 11:58:55	0	5208
 11:58:55	0	5208
 11:58:56	0	5208
 11:58:56	0	5208
 11:58:56	0	5208
 11:58:56	0	5208
 11:58:56	0	5208
 11:58:57	0	5208
 11:58:57	0	5208
 11:58:57	0	5208
 11:58:57	0	5208
 11:58:57	0	5208
 11:58:58	0	5208
 11:58:58	0	5208
 11:58:58	0	5208
 11:58:58	0	5208
 11:58:58	0	5208
 11:58:59	0	5208
 11:58:59	0	5208
 11:58:59	0	5208
 11:58:59	0	5208
 11:58:59	0	5208
 11:59:00	0	5208
 11:59:00	0	5208
 11:59:00	0	5208
 11:59:00	0	5208
 11:59:00	0	5208
 11:59:01	0	5208
 11:59:01	0	5208
 11:59:01	0	5208
 11:59:01	0	5208
 11:59:01	0	5208
 11:59:02	0	5208
 11:59:02	0	5208
 11:59:02	0	5208
 11:59:02	0	5208
 11:59:02	0	5208
 11:59:03	0	5208
 11:59:03	0	5208
 11:59:03	0	5208
 11:59:03	0	5208
 11:59:03	0	5208
 11:59:04	0	5208
 11:59:04	0	5208
 11:59:04	0	5208
 11:59:04	0	5208
 11:59:04	0	5208
 11:59:05	0	5208
 11:59:05	0	5208
 11:59:05	0	5208
 11:59:05	0	5208
 11:59:05	0	5208
 11:59:06	0	5208
 11:59:06	0	5208
 11:59:06	0	5208
 11:59:06	0	5208
 11:59:06	0	5208
 11:59:07	0	5208
 11:59:07	0	5208
 11:59:07	0	5208
 11:59:07	0	5208
 11:59:07	0	5208
 11:59:08	0	5208
 11:59:08	0	5208
 11:59:08	0	5208
 11:59:08	0	5208
 11:59:08	0	5208
 11:59:09	0	5208
 11:59:09	0	5208
 11:59:09	0	5208
 11:59:09	0	5208
 11:59:09	0	5208
 11:59:10	0	5208
 11:59:10	0	5208
 11:59:10	0	5208
 11:59:10	0	5208
 11:59:10	0	5208
 11:59:11	0	5208
 11:59:11	0	5208
 11:59:11	0	5208
 11:59:11	0	5208
 11:59:11	0	5208
 11:59:12	0	5208
 11:59:12	0	5208
 11:59:12	0	5208
 11:59:12	0	5208
 11:59:12	0	5208
 11:59:13	0	5208
 11:59:13	0	5208
 11:59:13	0	5208
 11:59:13	0	5208
 11:59:13	0	5208
 11:59:14	0	5208
 11:59:14	0	5208
 11:59:14	0	5208
 11:59:14	0	5208
 11:59:14	0	5208
 11:59:15	0	5208
 11:59:15	0	5208
 11:59:15	0	5208
 11:59:15	0	5208
 11:59:15	0	5208
 11:59:16	0	5208
 11:59:16	0	5208
 11:59:16	0	5208
 11:59:16	0	5208
 11:59:16	0	5208
 11:59:17	0	5208
 11:59:17	0	5208
 11:59:17	0	5208
 11:59:17	0	5208
 11:59:17	0	5208
 11:59:18	0	5208
 11:59:18	0	5208
 11:59:18	0	5208
 11:59:18	0	5208
 11:59:18	0	5208
 11:59:19	0	5208
 11:59:19	0	5208
 11:59:19	0	5208
 11:59:19	0	5208
 11:59:19	0	5208
 11:59:20	0	5208
 11:59:20	0	5208
 11:59:20	0	5208
 11:59:20	0	5208
 11:59:20	0	5208
 11:59:21	0	5208
 11:59:21	0	5208
 11:59:21	0	5208
 11:59:21	0	5208
 11:59:21	0	5208
 11:59:22	0	5208
 11:59:22	0	5208
 11:59:22	0	5208
 11:59:22	0	5208
 11:59:22	0	5208
 11:59:23	0	5208
 11:59:23	0	5208
 11:59:23	0	5208
 11:59:23	0	5208
 11:59:23	0	5208
 11:59:24	0	5208
 11:59:24	0	5208
 11:59:24	0	5208
 11:59:24	0	5208
 11:59:24	0	5208
 11:59:25	0	5208
 11:59:25	0	5208
 11:59:25	0	5208
 11:59:25	0.318	5208
 11:59:25	0.334	5208
 11:59:26	0.334	5208
 11:59:26	0.334	5208
 11:59:26	0.334	5209
 11:59:26	0	5209
 11:59:26	0	5209
 11:59:27	0	5209
 11:59:27	0	5209
 11:59:27	0	5209
 11:59:27	0	5209
 11:59:27	0	5209
 11:59:28	0.437	5209
 11:59:28	0.447	5210
 11:59:28	0.457	5210
 11:59:28	0.468	5210
 11:59:28	0.473	5210
 11:59:29	0.473	5210
 11:59:29	0.473	5210
 11:59:29	0.473	5210
 11:59:29	0.457	5211
 11:59:29	0.447	5211
 11:59:30	0.426	5211
 11:59:30	0.411	5211
 11:59:30	0.396	5211
 11:59:30	0.385	5212
 11:59:30	0.385	5212
 11:59:31	0.385	5212
 11:59:31	0.38	5212
 11:59:31	0.38	5212
 11:59:31	0.38	5213
 11:59:31	0	5213
 11:59:32	0	5213
 11:59:32	0	5213
 11:59:32	0	5213
 11:59:32	0.349	5214
 11:59:32	0.349	5214
 11:59:33	0.349	5214
 11:59:33	0.349	5214
 11:59:33	0.339	5214
 11:59:33	0	5214
 11:59:33	0	5214
 11:59:34	0	5214
 11:59:34	0	5214
 11:59:34	0	5214
 11:59:34	0	5214
 11:59:34	0.246	5214
 11:59:35	0.246	5214
 11:59:35	0.246	5214
 11:59:35	0.246	5214
 11:59:35	0.241	5214
 11:59:35	0	5214
 11:59:36	0	5214
 11:59:36	0	5214
 11:59:36	0	5214
 11:59:36	0.252	5214
 11:59:36	0.257	5214
 11:59:37	0.257	5215
 11:59:37	0.262	5215
 11:59:37	0.262	5215
 11:59:37	0.267	5215
 11:59:37	0.282	5215
 11:59:38	0.282	5215
 11:59:38	0.282	5215
 11:59:38	0.282	5215
 11:59:38	0.288	5215
 11:59:38	0.288	5215
 11:59:39	0.288	5215
 11:59:39	0.293	5215
 11:59:39	0.298	5215
 11:59:39	0.298	5215
 11:59:39	0.298	5215
 11:59:40	0.298	5216
 11:59:40	0.298	5216
 11:59:40	0.298	5216
 11:59:40	0.298	5216
 11:59:40	0.293	5216
 11:59:41	0.282	5216
 11:59:41	0.277	5216
 11:59:41	0.272	5216
 11:59:41	0.267	5216
 11:59:41	0.267	5216
 11:59:42	0.262	5216
 11:59:42	0.257	5216
 11:59:42	0.252	5216
 11:59:42	0.246	5216
 11:59:42	0.241	5216
 11:59:43	0.226	5216
 11:59:43	0.216	5216
 11:59:43	0.205	5216
 11:59:43	0.2	5216
 11:59:43	0	5216
 11:59:44	0	5216
 11:59:44	0	5216
 11:59:44	0	5216
 11:59:44	0	5216
 11:59:44	0	5216
 11:59:45	0	5216
 11:59:45	0	5216
 11:59:45	0	5216
 11:59:45	0	5216
 11:59:45	0	5216
 11:59:46	0	5216
 11:59:46	0	5216
 11:59:46	0	5216
 11:59:46	0	5216
 11:59:46	0.303	5217
 11:59:47	0.313	5218
 11:59:47	0.426	5219
 11:59:47	0.545	5220
 11:59:47	1.028	5221
 11:59:47	1.167	5221
 11:59:48	1.183	5221
 11:59:48	1.183	5221
 11:59:48	1.183	5222
 11:59:48	1.203	5222
 11:59:48	1.183	5222
 11:59:49	1.172	5222
 11:59:49	1.167	5223
 11:59:49	1.064	5223
 11:59:49	1.044	5223
 11:59:49	0.987	5223
 11:59:50	0.91	5223
 11:59:50	0.91	5224
 11:59:50	0.89	5224
 11:59:50	0.89	5224
 11:59:50	0.89	5224
 11:59:51	0.771	5224
 11:59:51	0.617	5225
 11:59:51	0.617	5225
 11:59:51	0.617	5225
 11:59:51	0.617	5225
 11:59:52	0.627	5225
 11:59:52	0.643	5225
 11:59:52	0.653	5225
 11:59:52	0.781	5225
 11:59:52	0.864	5226
 11:59:53	0.879	5226
 11:59:53	1.034	5226
 11:59:53	1.064	5226
 11:59:53	1.064	5226
 11:59:53	1.131	5227
 11:59:54	1.172	5227
 11:59:54	1.172	5227
 11:59:54	1.26	5227
 11:59:54	1.347	5228
 11:59:54	1.358	5228
 11:59:55	1.347	5228
 11:59:55	1.347	5228
 11:59:55	1.358	5229
 11:59:55	1.383	5229
 11:59:55	1.419	5229
 11:59:56	1.45	5230
 11:59:56	1.45	5230
 11:59:56	1.455	5230
 11:59:56	1.45	5230
 11:59:56	1.45	5231
 11:59:57	1.455	5231
 11:59:57	1.476	5231
 11:59:57	1.481	5232
 11:59:57	1.538	5232
 11:59:57	1.538	5232
 11:59:58	1.558	5233
 11:59:58	1.584	5233
 11:59:58	1.558	5233
 11:59:58	1.558	5233
 11:59:58	1.62	5234
 11:59:59	1.63	5234
 11:59:59	1.63	5234
 11:59:59	1.62	5235
 11:59:59	1.491	5235
 11:59:59	1.61	5235
 12:00:00	1.491	5236
 12:00:00	1.491	5236
 12:00:00	1.491	5236
 12:00:00	1.476	5236
 12:00:00	1.502	5237
 12:00:01	1.502	5237
 12:00:01	1.476	5237
 12:00:01	1.476	5238
 12:00:01	1.476	5238
 12:00:01	1.353	5238
 12:00:02	1.353	5238
 12:00:02	1.353	5239
 12:00:02	1.353	5239
 12:00:02	1.43	5239
 12:00:02	1.43	5240
 12:00:03	1.61	5240
 12:00:03	1.61	5240
 12:00:03	1.61	5241
 12:00:03	1.625	5241
 12:00:03	1.61	5241
 12:00:04	1.625	5241
 12:00:04	1.625	5242
 12:00:04	1.625	5242
 12:00:04	1.625	5242
 12:00:04	1.533	5243
 12:00:05	1.533	5243
 12:00:05	1.594	5243
 12:00:05	1.533	5244
 12:00:05	1.594	5244
 12:00:05	1.533	5244
 12:00:06	1.594	5245
 12:00:06	1.61	5245
 12:00:06	1.594	5245
 12:00:06	1.455	5246
 12:00:06	1.548	5246
 12:00:07	1.533	5246
 12:00:07	1.548	5246
 12:00:07	1.533	5247
 12:00:07	1.533	5247
 12:00:07	1.533	5247
 12:00:08	1.533	5248
 12:00:08	1.533	5248
 12:00:08	1.533	5248
 12:00:08	1.481	5249
 12:00:08	1.481	5249
 12:00:09	1.44	5249
 12:00:09	1.481	5249
 12:00:09	1.527	5250
 12:00:09	1.481	5250
 12:00:09	1.527	5250
 12:00:10	1.527	5251
 12:00:10	1.466	5251
 12:00:10	1.538	5251