Excel "Strava Best Effort" Calculation

[johnmpc]

Hey All, I'm working on a personal project that has me stumped at the moment. This might not be possible with a straight forward formula, but hopefully it is.

I have an activity with distance travelled every second. I want to calculate the shortest time it took to travel predefined distances within the activity.

So the activity I have attached was 2 hours long and 24km but i could only put up a small segment. With in that I want to find the quickest time it took to cover 1km and some other distances but lets start with 1km.

So i think what i want is the count of Time cells for the minumum number of distance travelled cells where the sum of distance travelled cells = 1km.

Any help would be greatly appreciated.

Timstamp	Time	Distance	Distance Traveled
02:04:52​	00:00:00​	1.5​	1.5​
02:04:53​	00:00:01​	3.16​	1.66​
02:04:54​	00:00:02​	5.28​	2.12​
02:04:55​	00:00:03​	7.5​	2.22​
02:04:56​	00:00:04​	10.13​	2.63​
02:04:57​	00:00:05​	12.62​	2.49​
02:04:58​	00:00:06​	15.49​	2.87​
02:04:59​	00:00:07​	18.58​	3.09​
02:05:00​	00:00:08​	21.8​	3.22​
02:05:01​	00:00:09​	24.55​	2.75​
02:05:02​	00:00:10​	27.29​	2.74​
02:05:03​	00:00:11​	29.82​	2.53​
02:05:04​	00:00:12​	32.75​	2.93​
02:05:05​	00:00:13​	35.75​	3​
02:05:06​	00:00:14​	38.26​	2.51​
02:05:07​	00:00:15​	41.16​	2.9​
02:05:08​	00:00:16​	43.79​	2.63​
02:05:09​	00:00:17​	46.4​	2.61​
02:05:10​	00:00:18​	49.27​	2.87​
02:05:11​	00:00:19​	52.39​	3.12​
02:05:12​	00:00:20​	55.43​	3.04​
02:05:13​	00:00:21​	58.44​	3.01​
02:05:14​	00:00:22​	61.12​	2.68​
02:05:15​	00:00:23​	64.01​	2.89​
02:05:16​	00:00:24​	66.63​	2.62​
02:05:17​	00:00:25​	68.98​	2.35​
02:05:18​	00:00:26​	71.52​	2.54​
02:05:19​	00:00:27​	74.05​	2.53​
02:05:20​	00:00:28​	77.36​	3.31​
02:05:21​	00:00:29​	80.65​	3.29​
02:05:22​	00:00:30​	83.83​	3.18​
02:05:23​	00:00:31​	87.2​	3.37​
02:05:24​	00:00:32​	89.97​	2.77​
02:05:25​	00:00:33​	92.98​	3.01​
02:05:26​	00:00:34​	95.76​	2.78​
02:05:27​	00:00:35​	98.78​	3.02​
02:05:28​	00:00:36​	101.86​	3.08​
02:05:29​	00:00:37​	104.18​	2.32​
02:05:30​	00:00:38​	106.99​	2.81​
02:05:31​	00:00:39​	109.56​	2.57​
02:05:32​	00:00:40​	112.6​	3.04​
02:05:33​	00:00:41​	116.12​	3.52​
02:05:34​	00:00:42​	119.26​	3.14​
02:05:35​	00:00:43​	122.36​	3.1​
02:05:36​	00:00:44​	125.7​	3.34​
02:05:37​	00:00:45​	129.03​	3.33​
02:05:38​	00:00:46​	132.51​	3.48​
02:05:39​	00:00:47​	135.96​	3.45​
02:05:40​	00:00:48​	139.11​	3.15​
02:05:41​	00:00:49​	142.17​	3.06​
02:05:42​	00:00:50​	145.31​	3.14​
02:05:43​	00:00:51​	148.7​	3.39​
02:05:44​	00:00:52​	151.6​	2.9​
02:05:45​	00:00:53​	154.13​	2.53​
02:05:46​	00:00:54​	157.28​	3.15​
02:05:47​	00:00:55​	160.05​	2.77​
02:05:48​	00:00:56​	162.86​	2.81​
02:05:49​	00:00:57​	166.59​	3.73​
02:05:50​	00:00:58​	169.8​	3.21​
02:05:51​	00:00:59​	173.73​	3.93​
02:05:52​	00:01:00​	176.69​	2.96​
02:05:53​	00:01:01​	179.17​	2.48​
02:05:54​	00:01:02​	181.82​	2.65​
02:05:55​	00:01:03​	184.31​	2.49​
02:05:56​	00:01:04​	187.65​	3.34​
02:05:57​	00:01:05​	191.01​	3.36​
02:05:58​	00:01:06​	194.47​	3.46​
02:05:59​	00:01:07​	197.76​	3.29​
02:06:00​	00:01:08​	201.25​	3.49​
02:06:01​	00:01:09​	204.94​	3.69​
02:06:02​	00:01:10​	207.95​	3.01​
02:06:03​	00:01:11​	210.88​	2.93​
02:06:04​	00:01:12​	213.73​	2.85​
02:06:05​	00:01:13​	216.87​	3.14​
02:06:06​	00:01:14​	220.21​	3.34​
02:06:07​	00:01:15​	223.15​	2.94​
02:06:08​	00:01:16​	226.2​	3.05​
02:06:09​	00:01:17​	229.1​	2.9​
02:06:10​	00:01:18​	232.21​	3.11​
02:06:11​	00:01:19​	234.87​	2.66​
02:06:12​	00:01:20​	238.21​	3.34​
02:06:13​	00:01:21​	241.8​	3.59​
02:06:14​	00:01:22​	244.89​	3.09​
02:06:15​	00:01:23​	248.22​	3.33​
02:06:16​	00:01:24​	251.25​	3.03​
02:06:17​	00:01:25​	254.13​	2.88​
02:06:18​	00:01:26​	257.03​	2.9​
02:06:19​	00:01:27​	260.33​	3.3​
02:06:20​	00:01:28​	263.02​	2.69​
02:06:21​	00:01:29​	265.91​	2.89​
02:06:22​	00:01:30​	268.81​	2.9​
02:06:23​	00:01:31​	272.07​	3.26​
02:06:24​	00:01:32​	275.13​	3.06​
02:06:25​	00:01:33​	277.66​	2.53​
02:06:26​	00:01:34​	280.37​	2.71​
02:06:27​	00:01:35​	283.77​	3.4​
02:06:28​	00:01:36​	286.29​	2.52​
02:06:29​	00:01:37​	288.99​	2.7​
02:06:30​	00:01:38​	291.84​	2.85​
02:06:31​	00:01:39​	294.78​	2.94​
02:06:32​	00:01:40​	298.09​	3.31​
02:06:33​	00:01:41​	301.25​	3.16​
02:06:34​	00:01:42​	304.32​	3.07​
02:06:35​	00:01:43​	304.32​	0​
02:06:36​	00:01:44​	307.29​	2.97​
02:06:37​	00:01:45​	310.65​	3.36​
02:06:38​	00:01:46​	313.9​	3.25​
02:06:39​	00:01:47​	316.9​	3​
02:06:40​	00:01:48​	319.74​	2.84​
02:06:41​	00:01:49​	323.03​	3.29​
02:06:42​	00:01:50​	326.57​	3.54​
02:06:43​	00:01:51​	329.3​	2.73​
02:06:44​	00:01:52​	331.95​	2.65​
02:06:45​	00:01:53​	334.63​	2.68​
02:06:46​	00:01:54​	337.42​	2.79​
02:06:47​	00:01:55​	340.2​	2.78​
02:06:48​	00:01:56​	343.31​	3.11​
02:06:49​	00:01:57​	346.45​	3.14​
02:06:50​	00:01:58​	349.19​	2.74​
02:06:51​	00:01:59​	351.99​	2.8​
02:06:52​	00:02:00​	355.14​	3.15​
02:06:53​	00:02:01​	357.9​	2.76​
02:06:54​	00:02:02​	360.99​	3.09​
02:06:55​	00:02:03​	363.87​	2.88​
02:06:56​	00:02:04​	366.29​	2.42​
02:06:57​	00:02:05​	369.3​	3.01​
02:06:58​	00:02:06​	375.15​	5.85​
02:06:59​	00:02:07​	375.15​	0​
02:07:00​	00:02:08​	378.35​	3.2​
02:07:01​	00:02:09​	381.36​	3.01​
02:07:02​	00:02:10​	384.36​	3​
02:07:03​	00:02:11​	387.45​	3.09​
02:07:04​	00:02:12​	390.23​	2.78​
02:07:05​	00:02:13​	393.19​	2.96​
02:07:06​	00:02:14​	396.48​	3.29​
02:07:07​	00:02:15​	399.3​	2.82​
02:07:08​	00:02:16​	401.96​	2.66​
02:07:09​	00:02:17​	404.54​	2.58​
02:07:10​	00:02:18​	407.51​	2.97​
02:07:11​	00:02:19​	410.78​	3.27​
02:07:12​	00:02:20​	414.03​	3.25​
02:07:13​	00:02:21​	417.37​	3.34​
02:07:14​	00:02:22​	420.6​	3.23​
02:07:15​	00:02:23​	423.8​	3.2​
02:07:16​	00:02:24​	426.86​	3.06​
02:07:17​	00:02:25​	429.84​	2.98​
02:07:18​	00:02:26​	432.63​	2.79​
02:07:19​	00:02:27​	435.55​	2.92​
02:07:20​	00:02:28​	438.33​	2.78​
02:07:21​	00:02:29​	441.33​	3​
02:07:22​	00:02:30​	444.62​	3.29​
02:07:23​	00:02:31​	447.73​	3.11​
02:07:24​	00:02:32​	450.78​	3.05​
02:07:25​	00:02:33​	453.15​	2.37​
02:07:26​	00:02:34​	455.78​	2.63​
02:07:27​	00:02:35​	458.36​	2.58​
02:07:28​	00:02:36​	461.53​	3.17​
02:07:29​	00:02:37​	464.9​	3.37​
02:07:30​	00:02:38​	468.76​	3.86​
02:07:31​	00:02:39​	472.09​	3.33​
02:07:32​	00:02:40​	479.24​	7.15​
02:07:33​	00:02:41​	479.24​	0​
02:07:34​	00:02:42​	482.05​	2.81​
02:07:35​	00:02:43​	484.67​	2.62​
02:07:36​	00:02:44​	487.44​	2.77​
02:07:37​	00:02:45​	489.4​	1.96​
02:07:38​	00:02:46​	491.65​	2.25​
02:07:39​	00:02:47​	494.7​	3.05​
02:07:40​	00:02:48​	497.29​	2.59​
02:07:41​	00:02:49​	500.13​	2.84​
02:07:42​	00:02:50​	503.24​	3.11​
02:07:43​	00:02:51​	506.64​	3.4​
02:07:44​	00:02:52​	510.28​	3.64​
02:07:45​	00:02:53​	513.3​	3.02​
02:07:46​	00:02:54​	516.52​	3.22​
02:07:47​	00:02:55​	519.65​	3.13​
02:07:48​	00:02:56​	522.87​	3.22​
02:07:49​	00:02:57​	525.81​	2.94​
02:07:50​	00:02:58​	528.73​	2.92​
02:07:51​	00:02:59​	531.52​	2.79​
02:07:52​	00:03:00​	534.72​	3.2​
02:07:53​	00:03:01​	537.79​	3.07​
02:07:54​	00:03:02​	540.92​	3.13​
02:07:55​	00:03:03​	543.55​	2.63​
02:07:56​	00:03:04​	546.23​	2.68​
02:07:57​	00:03:05​	549.15​	2.92​
02:07:58​	00:03:06​	552.14​	2.99​
02:07:59​	00:03:07​	555.16​	3.02​
02:08:00​	00:03:08​	558.33​	3.17​
02:08:01​	00:03:09​	560.82​	2.49​
02:08:02​	00:03:10​	563.64​	2.82​
02:08:03​	00:03:11​	566.56​	2.92​
02:08:04​	00:03:12​	569.77​	3.21​
02:08:05​	00:03:13​	572.63​	2.86​
02:08:06​	00:03:14​	575.55​	2.92​
02:08:07​	00:03:15​	579.14​	3.59​
02:08:08​	00:03:16​	582.26​	3.12​
02:08:09​	00:03:17​	585.79​	3.53​
02:08:10​	00:03:18​	589.78​	3.99​
02:08:11​	00:03:19​	593.85​	4.07​
02:08:12​	00:03:20​	597.47​	3.62​
02:08:13​	00:03:21​	600.38​	2.91​
02:08:14​	00:03:22​	603.5​	3.12​
02:08:15​	00:03:23​	606.66​	3.16​
02:08:16​	00:03:24​	608.87​	2.21​
02:08:17​	00:03:25​	611.94​	3.07​
02:08:18​	00:03:26​	614.85​	2.91​
02:08:19​	00:03:27​	617.65​	2.8​
02:08:20​	00:03:28​	620.45​	2.8​
02:08:21​	00:03:29​	622.79​	2.34​
02:08:22​	00:03:30​	625.15​	2.36​
02:08:23​	00:03:31​	627.8​	2.65​
02:08:24​	00:03:32​	631.12​	3.32​
02:08:25​	00:03:33​	634.17​	3.05​
02:08:26​	00:03:34​	637.41​	3.24​
02:08:27​	00:03:35​	640.33​	2.92​
02:08:28​	00:03:36​	643.49​	3.16​
02:08:29​	00:03:37​	646.52​	3.03​
02:08:30​	00:03:38​	649.52​	3​
02:08:31​	00:03:39​	652.73​	3.21​
02:08:32​	00:03:40​	655.51​	2.78​
02:08:33​	00:03:41​	658.62​	3.11​
02:08:34​	00:03:42​	661.63​	3.01​
02:08:35​	00:03:43​	664.85​	3.22​
02:08:36​	00:03:44​	668.04​	3.19​
02:08:37​	00:03:45​	671.27​	3.23​
02:08:38​	00:03:46​	674.66​	3.39​
02:08:39​	00:03:47​	677.95​	3.29​
02:08:40​	00:03:48​	680.66​	2.71​
02:08:41​	00:03:49​	683.82​	3.16​
02:08:42​	00:03:50​	687.03​	3.21​
02:08:43​	00:03:51​	690.32​	3.29​
02:08:44​	00:03:52​	693.66​	3.34​
02:08:45​	00:03:53​	697.11​	3.45​
02:08:46​	00:03:54​	700.46​	3.35​
02:08:47​	00:03:55​	703.8​	3.34​
02:08:48​	00:03:56​	706.89​	3.09​
02:08:49​	00:03:57​	709.95​	3.06​
02:08:50​	00:03:58​	712.92​	2.97​
02:08:51​	00:03:59​	716.12​	3.2​
02:08:52​	00:04:00​	719.02​	2.9​
02:08:53​	00:04:01​	721.95​	2.93​
02:08:54​	00:04:02​	724.84​	2.89​
02:08:55​	00:04:03​	728.52​	3.68​
02:08:56​	00:04:04​	731.63​	3.11​
02:08:57​	00:04:05​	734.67​	3.04​
02:08:58​	00:04:06​	737.48​	2.81​
02:08:59​	00:04:07​	740.41​	2.93​
02:09:00​	00:04:08​	744.02​	3.61​
02:09:01​	00:04:09​	747.25​	3.23​
02:09:02​	00:04:10​	750.38​	3.13​
02:09:03​	00:04:11​	753.22​	2.84​
02:09:04​	00:04:12​	756.16​	2.94​
02:09:05​	00:04:13​	759.03​	2.87​
02:09:06​	00:04:14​	761.79​	2.76​
02:09:07​	00:04:15​	764.88​	3.09​
02:09:08​	00:04:16​	768.1​	3.22​
02:09:09​	00:04:17​	771.17​	3.07​
02:09:10​	00:04:18​	774.51​	3.34​
02:09:11​	00:04:19​	777.72​	3.21​
02:09:12​	00:04:20​	781.09​	3.37​
02:09:13​	00:04:21​	784.22​	3.13​
02:09:14​	00:04:22​	787.05​	2.83​
02:09:15​	00:04:23​	789.83​	2.78​
02:09:16​	00:04:24​	792.68​	2.85​
02:09:17​	00:04:25​	795.42​	2.74​
02:09:18​	00:04:26​	798.34​	2.92​
02:09:19​	00:04:27​	801.42​	3.08​
02:09:20​	00:04:28​	804.81​	3.39​
02:09:21​	00:04:29​	808.04​	3.23​
02:09:22​	00:04:30​	811.38​	3.34​
02:09:23​	00:04:31​	814.66​	3.28​
02:09:24​	00:04:32​	817.95​	3.29​
02:09:25​	00:04:33​	820.85​	2.9​
02:09:26​	00:04:34​	824.47​	3.62​
02:09:27​	00:04:35​	827.5​	3.03​
02:09:28​	00:04:36​	830.45​	2.95​
02:09:29​	00:04:37​	833.39​	2.94​
02:09:30​	00:04:38​	836.49​	3.1​
02:09:31​	00:04:39​	839.2​	2.71​
02:09:32​	00:04:40​	842.57​	3.37​
02:09:33​	00:04:41​	845.71​	3.14​
02:09:34​	00:04:42​	848.72​	3.01​
02:09:35​	00:04:43​	851.73​	3.01​
02:09:36​	00:04:44​	854.96​	3.23​
02:09:37​	00:04:45​	857.8​	2.84​
02:09:38​	00:04:46​	860.62​	2.82​
02:09:39​	00:04:47​	863.75​	3.13​
02:09:40​	00:04:48​	866.8​	3.05​
02:09:41​	00:04:49​	869.78​	2.98​
02:09:42​	00:04:50​	872.97​	3.19​
02:09:43​	00:04:51​	875.9​	2.93​
02:09:44​	00:04:52​	879.06​	3.16​
02:09:45​	00:04:53​	882.31​	3.25​
02:09:46​	00:04:54​	885.53​	3.22​
02:09:47​	00:04:55​	888.62​	3.09​
02:09:48​	00:04:56​	891.83​	3.21​
02:09:49​	00:04:57​	894.8​	2.97​
02:09:50​	00:04:58​	897.95​	3.15​
02:09:51​	00:04:59​	901.07​	3.12​
02:09:52​	00:05:00​	904.43​	3.36​
02:09:53​	00:05:01​	907.6​	3.17​
02:09:54​	00:05:02​	910.53​	2.93​
02:09:55​	00:05:03​	913.58​	3.05​
02:09:56​	00:05:04​	916.89​	3.31​
02:09:57​	00:05:05​	920.19​	3.3​
02:09:58​	00:05:06​	923.52​	3.33​
02:09:59​	00:05:07​	926.81​	3.29​
02:10:00​	00:05:08​	929.5​	2.69​
02:10:01​	00:05:09​	932.66​	3.16​
02:10:02​	00:05:10​	935.63​	2.97​
02:10:03​	00:05:11​	938.9​	3.27​
02:10:04​	00:05:12​	942.05​	3.15​
02:10:05​	00:05:13​	945.47​	3.42​
02:10:06​	00:05:14​	948.37​	2.9​
02:10:07​	00:05:15​	951.14​	2.77​
02:10:08​	00:05:16​	954.13​	2.99​
02:10:09​	00:05:17​	957.01​	2.88​
02:10:10​	00:05:18​	959.79​	2.78​
02:10:11​	00:05:19​	962.9​	3.11​
02:10:12​	00:05:20​	965.81​	2.91​
02:10:13​	00:05:21​	968.46​	2.65​
02:10:14​	00:05:22​	972.2​	3.74​
02:10:15​	00:05:23​	975.19​	2.99​
02:10:16​	00:05:24​	978.23​	3.04​
02:10:17​	00:05:25​	981.14​	2.91​
02:10:18​	00:05:26​	984.39​	3.25​
02:10:19​	00:05:27​	987.56​	3.17​
02:10:20​	00:05:28​	990.72​	3.16​
02:10:21​	00:05:29​	993.84​	3.12​
02:10:22​	00:05:30​	997.14​	3.3​
02:10:23​	00:05:31​	1000.27​	3.13​
02:10:24​	00:05:32​	1003.3​	3.03​
02:10:25​	00:05:33​	1006.49​	3.19​
02:10:26​	00:05:34​	1009.64​	3.15​
02:10:27​	00:05:35​	1012.63​	2.99​
02:10:28​	00:05:36​	1015.27​	2.64​
02:10:29​	00:05:37​	1018.33​	3.06​
02:10:30​	00:05:38​	1021.29​	2.96​
02:10:31​	00:05:39​	1024.69​	3.4​
02:10:32​	00:05:40​	1027.65​	2.96​
02:10:33​	00:05:41​	1030.72​	3.07​
02:10:34​	00:05:42​	1033.76​	3.04​
02:10:35​	00:05:43​	1037.16​	3.4​
02:10:36​	00:05:44​	1040.76​	3.6​
02:10:37​	00:05:45​	1043.92​	3.16​
02:10:38​	00:05:46​	1046.83​	2.91​
02:10:39​	00:05:47​	1050.13​	3.3​
02:10:40​	00:05:48​	1053.12​	2.99​
02:10:41​	00:05:49​	1056.14​	3.02​
02:10:42​	00:05:50​	1059.49​	3.35​
02:10:43​	00:05:51​	1062.99​	3.5​
02:10:44​	00:05:52​	1066​	3.01​
02:10:45​	00:05:53​	1069.23​	3.23​
02:10:46​	00:05:54​	1071.53​	2.3​
02:10:47​	00:05:55​	1074.56​	3.03​
02:10:48​	00:05:56​	1077.63​	3.07​
02:10:49​	00:05:57​	1081.02​	3.39​
02:10:50​	00:05:58​	1084.69​	3.67​
02:10:51​	00:05:59​	1087.86​	3.17​
02:10:52​	00:06:00​	1091.07​	3.21​
02:10:53​	00:06:01​	1094.34​	3.27​
02:10:54​	00:06:02​	1097.58​	3.24​
02:10:55​	00:06:03​	1100.71​	3.13​
02:10:56​	00:06:04​	1103.68​	2.97​
02:10:57​	00:06:05​	1106.62​	2.94​
02:10:58​	00:06:06​	1109.51​	2.89​
02:10:59​	00:06:07​	1112.52​	3.01​
02:11:00​	00:06:08​	1115.58​	3.06​
02:11:01​	00:06:09​	1118.7​	3.12​
02:11:02​	00:06:10​	1121.83​	3.13​
02:11:03​	00:06:11​	1124.82​	2.99​
02:11:04​	00:06:12​	1127.96​	3.14​
02:11:05​	00:06:13​	1131.1​	3.14​
02:11:06​	00:06:14​	1134.14​	3.04​
02:11:07​	00:06:15​	1137.37​	3.23​
02:11:08​	00:06:16​	1140.56​	3.19​
02:11:09​	00:06:17​	1143.76​	3.2​
02:11:10​	00:06:18​	1147​	3.24​
02:11:11​	00:06:19​	1150.16​	3.16​
02:11:12​	00:06:20​	1153.73​	3.57​
02:11:13​	00:06:21​	1156.93​	3.2​
02:11:14​	00:06:22​	1159.95​	3.02​
02:11:15​	00:06:23​	1162.91​	2.96​
02:11:16​	00:06:24​	1165.94​	3.03​
02:11:17​	00:06:25​	1169.12​	3.18​
02:11:18​	00:06:26​	1172.55​	3.43​
02:11:19​	00:06:27​	1175.47​	2.92​
02:11:20​	00:06:28​	1178.43​	2.96​
02:11:21​	00:06:29​	1181.24​	2.81​
02:11:22​	00:06:30​	1184.31​	3.07​
02:11:23​	00:06:31​	1187.18​	2.87​
02:11:24​	00:06:32​	1190.45​	3.27​
02:11:25​	00:06:33​	1193.68​	3.23​
02:11:26​	00:06:34​	1197.4​	3.72​
02:11:27​	00:06:35​	1200.44​	3.04​
02:11:28​	00:06:36​	1203.24​	2.8​
02:11:29​	00:06:37​	1206.23​	2.99​
02:11:30​	00:06:38​	1209.17​	2.94​
02:11:31​	00:06:39​	1212.22​	3.05​
02:11:32​	00:06:40​	1215.56​	3.34​
02:11:33​	00:06:41​	1218.83​	3.27​
02:11:34​	00:06:42​	1222.4​	3.57​
02:11:35​	00:06:43​	1225.44​	3.04​
02:11:36​	00:06:44​	1228.8​	3.36​
02:11:37​	00:06:45​	1232.1​	3.3​
02:11:38​	00:06:46​	1234.61​	2.51​
02:11:39​	00:06:47​	1237.51​	2.9​
02:11:40​	00:06:48​	1240.8​	3.29​
02:11:41​	00:06:49​	1243.81​	3.01​
02:11:42​	00:06:50​	1246.75​	2.94​
02:11:43​	00:06:51​	1249.75​	3​
02:11:44​	00:06:52​	1252.96​	3.21​
02:11:45​	00:06:53​	1255.93​	2.97​
02:11:46​	00:06:54​	1258.74​	2.81​
02:11:47​	00:06:55​	1261.81​	3.07​
02:11:48​	00:06:56​	1264.33​	2.52​
02:11:49​	00:06:57​	1267.68​	3.35​
02:11:50​	00:06:58​	1270.74​	3.06​
02:11:51​	00:06:59​	1274.17​	3.43​
02:11:52​	00:07:00​	1277.16​	2.99​
02:11:53​	00:07:01​	1280.16​	3​
02:11:54​	00:07:02​	1283.04​	2.88​
02:11:55​	00:07:03​	1286.04​	3​
02:11:56​	00:07:04​	1289.08​	3.04​
02:11:57​	00:07:05​	1292.55​	3.47​
02:11:58​	00:07:06​	1295.43​	2.88​
02:11:59​	00:07:07​	1298.43​	3​
02:12:00​	00:07:08​	1301.2​	2.77​
02:12:01​	00:07:09​	1304.23​	3.03​
02:12:02​	00:07:10​	1307.4​	3.17​
02:12:03​	00:07:11​	1310.28​	2.88​
02:12:04​	00:07:12​	1313.3​	3.02​
02:12:05​	00:07:13​	1316.55​	3.25​
02:12:06​	00:07:14​	1319.62​	3.07​
02:12:07​	00:07:15​	1322.61​	2.99​
02:12:08​	00:07:16​	1325.64​	3.03​
02:12:09​	00:07:17​	1328.64​	3​
02:12:10​	00:07:18​	1331.06​	2.42​
02:12:11​	00:07:19​	1334.18​	3.12​
02:12:12​	00:07:20​	1337.26​	3.08​
02:12:13​	00:07:21​	1340.28​	3.02​
02:12:14​	00:07:22​	1343.28​	3​
02:12:15​	00:07:23​	1346.23​	2.95​
02:12:16​	00:07:24​	1349.2​	2.97​
02:12:17​	00:07:25​	1352.43​	3.23​
02:12:18​	00:07:26​	1355.47​	3.04​
02:12:19​	00:07:27​	1358.78​	3.31​
02:12:20​	00:07:28​	1362.53​	3.75​
02:12:21​	00:07:29​	1365.95​	3.42​
02:12:22​	00:07:30​	1368.71​	2.76​
02:12:23​	00:07:31​	1371.59​	2.88​
02:12:24​	00:07:32​	1374.79​	3.2​
02:12:25​	00:07:33​	1378.27​	3.48​
02:12:26​	00:07:34​	1381.14​	2.87​
02:12:27​	00:07:35​	1383.86​	2.72​
02:12:28​	00:07:36​	1387​	3.14​
02:12:29​	00:07:37​	1390.17​	3.17​
02:12:30​	00:07:38​	1393.07​	2.9​
02:12:31​	00:07:39​	1396.21​	3.14​
02:12:32​	00:07:40​	1399.41​	3.2​
02:12:33​	00:07:41​	1402.89​	3.48​
02:12:34​	00:07:42​	1406.44​	3.55​
02:12:35​	00:07:43​	1409.81​	3.37​
02:12:36​	00:07:44​	1412.99​	3.18​
02:12:37​	00:07:45​	1416.05​	3.06​
02:12:38​	00:07:46​	1419.05​	3​
02:12:39​	00:07:47​	1422.1​	3.05​
02:12:40​	00:07:48​	1424.89​	2.79​
02:12:41​	00:07:49​	1428.01​	3.12​
02:12:42​	00:07:50​	1431.04​	3.03​
02:12:43​	00:07:51​	1434.05​	3.01​
02:12:44​	00:07:52​	1437.97​	3.92​
02:12:45​	00:07:53​	1440.93​	2.96​
02:12:46​	00:07:54​	1443.43​	2.5​
02:12:47​	00:07:55​	1446.27​	2.84​
02:12:48​	00:07:56​	1449.07​	2.8​
02:12:49​	00:07:57​	1452.07​	3​
02:12:50​	00:07:58​	1455.18​	3.11​
02:12:51​	00:07:59​	1458.19​	3.01​
02:12:52​	00:08:00​	1461.47​	3.28​
02:12:53​	00:08:01​	1464.57​	3.1​
02:12:54​	00:08:02​	1467.67​	3.1​
02:12:55​	00:08:03​	1470.72​	3.05​
02:12:56​	00:08:04​	1473.71​	2.99​
02:12:57​	00:08:05​	1476.67​	2.96​
02:12:58​	00:08:06​	1479.7​	3.03​
02:12:59​	00:08:07​	1482.7​	3​
02:13:00​	00:08:08​	1485.76​	3.06​
02:13:01​	00:08:09​	1488.51​	2.75​
02:13:02​	00:08:10​	1491.19​	2.68​
02:13:03​	00:08:11​	1494.22​	3.03​
02:13:04​	00:08:12​	1497.51​	3.29​
02:13:05​	00:08:13​	1500.96​	3.45​
02:13:06​	00:08:14​	1503.91​	2.95​
02:13:07​	00:08:15​	1507.4​	3.49​
02:13:08​	00:08:16​	1510.82​	3.42​
02:13:09​	00:08:17​	1513.96​	3.14​
02:13:10​	00:08:18​	1517.1​	3.14​
02:13:11​	00:08:19​	1520.44​	3.34​
02:13:12​	00:08:20​	1523.6​	3.16​
02:13:13​	00:08:21​	1526.64​	3.04​
02:13:14​	00:08:22​	1530.02​	3.38​
02:13:15​	00:08:23​	1533.21​	3.19​
02:13:16​	00:08:24​	1536.51​	3.3​
02:13:17​	00:08:25​	1539.42​	2.91​
02:13:18​	00:08:26​	1542.49​	3.07​
02:13:19​	00:08:27​	1546.24​	3.75​
02:13:20​	00:08:28​	1549.26​	3.02​
02:13:21​	00:08:29​	1552.43​	3.17​
02:13:22​	00:08:30​	1555.3​	2.87​
02:13:23​	00:08:31​	1558.66​	3.36​
02:13:24​	00:08:32​	1561.87​	3.21​
02:13:25​	00:08:33​	1564.87​	3​
02:13:26​	00:08:34​	1567.59​	2.72​
02:13:27​	00:08:35​	1570.91​	3.32​
02:13:28​	00:08:36​	1574.25​	3.34​
02:13:29​	00:08:37​	1577.42​	3.17​
02:13:30​	00:08:38​	1580.54​	3.12​
02:13:31​	00:08:39​	1583.78​	3.24​
02:13:32​	00:08:40​	1586.81​	3.03​
02:13:33​	00:08:41​	1590.02​	3.21​
02:13:34​	00:08:42​	1593.12​	3.1​
02:13:35​	00:08:43​	1596.11​	2.99​
02:13:36​	00:08:44​	1599.32​	3.21​
02:13:37​	00:08:45​	1602.28​	2.96​
02:13:38​	00:08:46​	1605.45​	3.17​
02:13:39​	00:08:47​	1608.55​	3.1​
02:13:40​	00:08:48​	1611.6​	3.05​
02:13:41​	00:08:49​	1614.87​	3.27​
02:13:42​	00:08:50​	1618.05​	3.18​
02:13:43​	00:08:51​	1620.74​	2.69​
02:13:44​	00:08:52​	1623.41​	2.67​
02:13:45​	00:08:53​	1626.36​	2.95​
02:13:46​	00:08:54​	1629.3​	2.94​
02:13:47​	00:08:55​	1632.69​	3.39​
02:13:48​	00:08:56​	1635.69​	3​
02:13:49​	00:08:57​	1638.54​	2.85​
02:13:50​	00:08:58​	1641.68​	3.14​
02:13:51​	00:08:59​	1644.42​	2.74​
02:13:52​	00:09:00​	1647.29​	2.87​
02:13:53​	00:09:01​	1650.25​	2.96​
02:13:54​	00:09:02​	1653.72​	3.47​
02:13:55​	00:09:03​	1657.12​	3.4​
02:13:56​	00:09:04​	1660.08​	2.96​
02:13:57​	00:09:05​	1663.03​	2.95​
02:13:58​	00:09:06​	1665.9​	2.87​
02:13:59​	00:09:07​	1669.18​	3.28​
02:14:00​	00:09:08​	1672.23​	3.05​
02:14:01​	00:09:09​	1675.85​	3.62​
02:14:02​	00:09:10​	1679.08​	3.23​
02:14:03​	00:09:11​	1682.52​	3.44​
02:14:04​	00:09:12​	1685.86​	3.34​
02:14:05​	00:09:13​	1689.15​	3.29​
02:14:06​	00:09:14​	1692.39​	3.24​
02:14:07​	00:09:15​	1695.59​	3.2​
02:14:08​	00:09:16​	1698.24​	2.65​
02:14:09​	00:09:17​	1701.32​	3.08​
02:14:10​	00:09:18​	1704.63​	3.31​
02:14:11​	00:09:19​	1707.8​	3.17​
02:14:12​	00:09:20​	1710.98​	3.18​
02:14:13​	00:09:21​	1714.12​	3.14​
02:14:14​	00:09:22​	1717.34​	3.22​
02:14:15​	00:09:23​	1720.42​	3.08​
02:14:16​	00:09:24​	1723.67​	3.25​
02:14:17​	00:09:25​	1726.84​	3.17​
02:14:18​	00:09:26​	1730.29​	3.45​
02:14:19​	00:09:27​	1733.28​	2.99​
02:14:20​	00:09:28​	1736.32​	3.04​
02:14:21​	00:09:29​	1739.13​	2.81​
02:14:22​	00:09:30​	1742.13​	3​
02:14:23​	00:09:31​	1745.36​	3.23​
02:14:24​	00:09:32​	1748.8​	3.44​
02:14:25​	00:09:33​	1752.16​	3.36​
02:14:26​	00:09:34​	1755.14​	2.98​
02:14:27​	00:09:35​	1758.57​	3.43​
02:14:28​	00:09:36​	1761.88​	3.31​
02:14:29​	00:09:37​	1764.85​	2.97​
02:14:30​	00:09:38​	1767.89​	3.04​
02:14:31​	00:09:39​	1771.29​	3.4​
02:14:32​	00:09:40​	1773.97​	2.68​
02:14:33​	00:09:41​	1777.13​	3.16​
02:14:34​	00:09:42​	1780.23​	3.1​
02:14:35​	00:09:43​	1783.7​	3.47​
02:14:36​	00:09:44​	1786.54​	2.84​
02:14:37​	00:09:45​	1789.73​	3.19​
02:14:38​	00:09:46​	1793.77​	4.04​
02:14:39​	00:09:47​	1797.07​	3.3​
02:14:40​	00:09:48​	1800.19​	3.12​
02:14:41​	00:09:49​	1803.54​	3.35​
02:14:42​	00:09:50​	1807.05​	3.51​
02:14:43​	00:09:51​	1810.32​	3.27​
02:14:44​	00:09:52​	1813.46​	3.14​
02:14:45​	00:09:53​	1816.86​	3.4​
02:14:46​	00:09:54​	1820.1​	3.24​
02:14:47​	00:09:55​	1823.33​	3.23​
02:14:48​	00:09:56​	1826.86​	3.53​
02:14:49​	00:09:57​	1829.79​	2.93​
02:14:50​	00:09:58​	1832.83​	3.04​
02:14:51​	00:09:59​	1835.83​	3​
02:14:52​	00:10:00​	1839.63​	3.8​
02:14:53​	00:10:01​	1842.98​	3.35​
02:14:54​	00:10:02​	1846.28​	3.3​
02:14:55​	00:10:03​	1849.71​	3.43​
02:14:56​	00:10:04​	1852.88​	3.17​
02:14:57​	00:10:05​	1855.84​	2.96​
02:14:58​	00:10:06​	1858.85​	3.01​
02:14:59​	00:10:07​	1862.17​	3.32​
02:15:00​	00:10:08​	1865.43​	3.26​
02:15:01​	00:10:09​	1868.33​	2.9​
02:15:02​	00:10:10​	1871.44​	3.11​
02:15:03​	00:10:11​	1874.85​	3.41​
02:15:04​	00:10:12​	1877.65​	2.8​
02:15:05​	00:10:13​	1880.99​	3.34​
02:15:06​	00:10:14​	1884.36​	3.37​
02:15:07​	00:10:15​	1887.98​	3.62​
02:15:08​	00:10:16​	1890.97​	2.99​
02:15:09​	00:10:17​	1893.83​	2.86​
02:15:10​	00:10:18​	1897.22​	3.39​
02:15:11​	00:10:19​	1900.4​	3.18​
02:15:12​	00:10:20​	1903.48​	3.08​
02:15:13​	00:10:21​	1906.67​	3.19​
02:15:14​	00:10:22​	1909.67​	3​
02:15:15​	00:10:23​	1912.75​	3.08​
02:15:16​	00:10:24​	1915.93​	3.18​
02:15:17​	00:10:25​	1919.31​	3.38​
02:15:18​	00:10:26​	1922.8​	3.49​
02:15:19​	00:10:27​	1926.15​	3.35​
02:15:20​	00:10:28​	1929.48​	3.33​
02:15:21​	00:10:29​	1933.27​	3.79​
02:15:22​	00:10:30​	1936.91​	3.64​
02:15:23​	00:10:31​	1940.28​	3.37​
02:15:24​	00:10:32​	1943.55​	3.27​
02:15:25​	00:10:33​	1947.03​	3.48​
02:15:26​	00:10:34​	1950.54​	3.51​
02:15:27​	00:10:35​	1953.08​	2.54​
02:15:28​	00:10:36​	1956.31​	3.23​
02:15:29​	00:10:37​	1959.55​	3.24​
02:15:30​	00:10:38​	1962.79​	3.24​
02:15:31​	00:10:39​	1966.01​	3.22​
02:15:32​	00:10:40​	1969.74​	3.73​
02:15:33​	00:10:41​	1973.12​	3.38​
02:15:34​	00:10:42​	1976.11​	2.99​
02:15:35​	00:10:43​	1979.59​	3.48​
02:15:36​	00:10:44​	1983.08​	3.49​
02:15:37​	00:10:45​	1986.42​	3.34​
02:15:38​	00:10:46​	1989.73​	3.31​
02:15:39​	00:10:47​	1993.14​	3.41​
02:15:40​	00:10:48​	1996.35​	3.21​
02:15:41​	00:10:49​	1999.59​	3.24​
​	​	​	​
​	​	​	​

 

[johnmpc]

PS, modifying your Strava GPX files has landed people in Jail for less... KOM's aren't everything ...

Thanks for the advice. No intention of doing that. Just love a bit of strava and excel analysis.

PS i'm quick enough I have 9 KOMs anyway

 

[felixstraube]

I'm always astonished about the simplicity and efficiency of @Fluff 's formulas.
I took a little different approach (to begin with it is much less efficient, I'll have to work on that), and searched for all the posible timespans that cover a given distance (from our example 1000 m), but with an error, like 0.2 m so we search for every distance covered from 999,8 to 1000,2 m and got the times for those (second count actually).
Then I listed those results.
To get only distances over 1000 m, I set the distance to 1000,2 and the error to 0,2. So that all the distances from 1000 to 1000,4 are returned.
I got some interesting results. I also calculated the average velocity of each segment, and looked at the time it took and contrasted it with the time that returned Fluff's formula.
EDIT: now we get the same results. (i was counting wrong the seconds at first).

Strava.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Time	Distance	Distance Traveled						Start time	End time	Second count	Time	Velocity [km/h]	Distance covered		Fluff's result
2		00:00:00	1.5	1.5		Distance	1000.2	m		00:03:09	00:08:31	323	00:05:23	11.149	1000.33		00:05:23
3		00:00:01	3.16	1.66		Error	0.2	m		00:03:07	00:08:29	323	00:05:23	11.149	1000.29		323
4		00:00:02	5.28	2.12						00:03:19	00:08:41	323	00:05:23	11.148	1000.24
5		00:00:03	7.5	2.22						00:03:08	00:08:30	323	00:05:23	11.147	1000.14
6		00:00:04	10.13	2.63						00:03:06	00:08:28	323	00:05:23	11.147	1000.11
7		00:00:05	12.62	2.49						00:03:05	00:08:27	323	00:05:23	11.146	1000.01
8		00:00:06	15.49	2.87						00:02:49	00:08:12	324	00:05:24	11.114	1000.22
9		00:00:07	18.58	3.09						00:02:16	00:07:40	325	00:05:25	11.078	1000.11
10		00:00:08	21.8	3.22						00:02:01	00:07:26	326	00:05:26	11.047	1000.33
11		00:00:09	24.55	2.75						00:01:45	00:07:10	326	00:05:26	11.044	1000.11
12		00:00:10	27.29	2.74						00:01:56	00:07:21	326	00:05:26	11.044	1000.08
13		00:00:11	29.82	2.53						00:01:59	00:07:24	326	00:05:26	11.043	1000.01
14		00:00:12	32.75	2.93						00:01:41	00:07:07	327	00:05:27	11.013	1000.34
15		00:00:13	35.75	3						00:01:38	00:07:04	327	00:05:27	11.010	1000.09
16		00:00:14	38.26	2.51						00:00:57	00:06:23	327	00:05:27	11.010	1000.05
17		00:00:15	41.16	2.9						00:00:30	00:05:57	328	00:05:28	10.980	1000.37
18		00:00:16	43.79	2.63						00:00:29	00:05:56	328	00:05:28	10.979	1000.27
19		00:00:17	46.4	2.61						00:00:26	00:05:53	328	00:05:28	10.978	1000.25
20		00:00:18	49.27	2.87						00:00:27	00:05:54	328	00:05:28	10.976	1000.01
21		00:00:19	52.39	3.12						00:00:17	00:05:45	329	00:05:29	10.944	1000.13
22		00:00:20	55.43	3.04						00:00:11	00:05:40	330	00:05:30	10.913	1000.36
Sheet1

Cell Formulas
Range	Formula
J2:O26	J2	=LET( d, D2:D526, t, B2:B526, dist_check, $G$2, avc, INT(G2/AVERAGE(d)*0.9), error, $G$3, co, COUNT(d), r, SEQUENCE(co), res_1, MAKEARRAY(co-avc, co-avc, LAMBDA(x,y, LET(dist, IFERROR(SUM(FILTER(d, (r>=x)*(r<=(y+avc)))),0), IF((dist>(dist_check-error))*(dist<(dist_check+error)),TEXT(INDEX(t,x),"hh:mm:ss")&";"&TEXT(INDEX(t,y+avc),"hh:mm:ss")&";"& y+avc-x+1 &";"&dist,"")) )), res_2, TOCOL(res_1), res_3, FILTER(res_2, res_2<>""), startTime, CHOOSECOLS(TEXTSPLIT(res_3,";"),1), endTime, BYROW(res_3, LAMBDA(x, CHOOSECOLS(TEXTSPLIT(INDEX(x,1),";"),2))), secondCount, BYROW(res_3, LAMBDA(x, CHOOSECOLS(TEXTSPLIT(INDEX(x,1),";"),3))), time, secondCount*TIME(0,0,1), dist, BYROW(res_3, LAMBDA(x, CHOOSECOLS(TEXTSPLIT(INDEX(x,1),";"),4))), velocity, dist/(time*24)/1000, ret, HSTACK(startTime, endTime, secondCount, time, velocity, dist), SORT(ret,5,-1) )
Q2	Q2	=MIN(TOCOL(BYROW(D2:D526,LAMBDA(br,XMATCH(0,SCAN(G2,br:D526,LAMBDA(a,b,a-b)),-1))),2))*TIME(0,0,1)
Q3	Q3	=MIN(TOCOL(BYROW(D2:D526,LAMBDA(br,XMATCH(0,SCAN(G2,br:D526,LAMBDA(a,b,a-b)),-1))),2))
Dynamic array formulas.

 

[Fluff]

The formula I used is taking the 1st value after 1km is covered, but it looks as though you are taking the value just before 1km was covered.

 

[felixstraube]

Yes, i corrected it. (The 10 min didn't pass so I edited the previous post.)
I was counting wrong the seconds. I had to add 1 to the count.
As distance and error are set I take all the values over 1000 m and below 1000.4 m