Help with running relay time tracking sheet

[ajc623]

I am trying to create a sheet that will allow me to estimate the time for each runner (12 total) over 36 total legs for the Hood to Coast relay in a couple weeks. I have the layout below with these colums

F-runners name
G-leg #
H-total miles for that leg
I-estimated time that leg should take based on their pace
J-estimated start time based on all runners hitting their goal pace
K-estimated finish time based on all runners hitting their goal pace
L-I will enter the actual time each runner finishes their leg
M-will calculate the pace for each leg base on the previous runners finish time and the current runners finish time
N-will show the difference in actual finish time for each leg and our estimate to track overall team time
O-will give a revised start time for each runner based on overall team time
P-will give a revised finish time for each runner based on overall team time

I have most of the functions working well and thought I was done until I start to enter some made up time and got to the point we go passed midnight and am now getting odd numbers and I cant seem to figure out how to fix it (see below) -34:55 per mile pace. The formula I use above this to determine the pace is =IF(L16>0,(L16-L15)/H16,"") which is saying- if I enter a finish time for that leg to take the finish time of the previous leg (which will be that start time of the current) and subtract it from the current leg finish time and then divide by the total miles in that leg. Let me know if you have any questions of would like me to send you the file. Thanks

Andrew
[TABLE="width: 831"]
<colgroup><col span="3"><col><col><col><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD] [/TD]
[TD]Miles[/TD]
[TD]Est time[/TD]
[TD]Est Start Time[/TD]
[TD]Est Finish Time[/TD]
[TD]Actual Finish Time[/TD]
[TD]Pace[/TD]
[TD]Difference[/TD]
[TD]Revised Start Time[/TD]
[TD]Revised Finish Time[/TD]
[/TR]
[TR]
[TD]Nicole[/TD]
[TD]Leg #1[/TD]
[TD]5.64[/TD]
[TD]57:09[/TD]
[TD]1:15 PM[/TD]
[TD]2:12 PM[/TD]
[TD] [/TD]
[TD]-20:57[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Stebs[/TD]
[TD]Leg #2[/TD]
[TD]5.67[/TD]
[TD]54:20[/TD]
[TD]2:12 PM[/TD]
[TD]3:06 PM[/TD]
[TD]3:06 PM[/TD]
[TD]39:47[/TD]
[TD]-00:29[/TD]
[TD]6:52 PM[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Ryan[/TD]
[TD]Leg #3[/TD]
[TD]3.93[/TD]
[TD]33:09[/TD]
[TD]3:06 PM[/TD]
[TD]3:39 PM[/TD]
[TD]3:49 PM[/TD]
[TD]10:56[/TD]
[TD]09:22[/TD]
[TD]3:06 PM[/TD]
[TD]3:39 PM[/TD]
[/TR]
[TR]
[TD]Dom[/TD]
[TD]Leg #4[/TD]
[TD]7.18[/TD]
[TD]70:36[/TD]
[TD]3:39 PM[/TD]
[TD]4:50 PM[/TD]
[TD]4:45 PM[/TD]
[TD]07:48[/TD]
[TD]-05:14[/TD]
[TD]3:49 PM[/TD]
[TD]4:59 PM[/TD]
[/TR]
[TR]
[TD]Andrew[/TD]
[TD]Leg #5[/TD]
[TD]6.08[/TD]
[TD]46:37[/TD]
[TD]4:50 PM[/TD]
[TD]5:36 PM[/TD]
[TD]5:36 PM[/TD]
[TD]08:23[/TD]
[TD]-00:51[/TD]
[TD]4:45 PM[/TD]
[TD]5:31 PM[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]Leg #6[/TD]
[TD]6.82[/TD]
[TD]61:36[/TD]
[TD]5:36 PM[/TD]
[TD]6:38 PM[/TD]
[TD]6:35 PM[/TD]
[TD]08:39[/TD]
[TD]-03:27[/TD]
[TD]5:36 PM[/TD]
[TD]6:37 PM[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Leg #7[/TD]
[TD]5.48[/TD]
[TD]43:34[/TD]
[TD]6:38 PM[/TD]
[TD]7:22 PM[/TD]
[TD]7:22 PM[/TD]
[TD]08:35[/TD]
[TD]-00:01[/TD]
[TD]6:35 PM[/TD]
[TD]7:18 PM[/TD]
[/TR]
[TR]
[TD]Cary[/TD]
[TD]Leg #8[/TD]
[TD]4.55[/TD]
[TD]43:23[/TD]
[TD]7:22 PM[/TD]
[TD]8:05 PM[/TD]
[TD]8:06 PM[/TD]
[TD]09:40[/TD]
[TD]00:36[/TD]
[TD]7:22 PM[/TD]
[TD]8:05 PM[/TD]
[/TR]
[TR]
[TD]Asa[/TD]
[TD]Leg #9[/TD]
[TD]6.91[/TD]
[TD]53:54[/TD]
[TD]8:05 PM[/TD]
[TD]8:59 PM[/TD]
[TD]8:58 PM[/TD]
[TD]07:32[/TD]
[TD]-01:18[/TD]
[TD]8:06 PM[/TD]
[TD]8:59 PM[/TD]
[/TR]
[TR]
[TD]Mel[/TD]
[TD]Leg #10[/TD]
[TD]5.12[/TD]
[TD]50:41[/TD]
[TD]8:59 PM[/TD]
[TD]9:49 PM[/TD]
[TD]9:45 PM[/TD]
[TD]09:11[/TD]
[TD]-04:59[/TD]
[TD]8:58 PM[/TD]
[TD]9:48 PM[/TD]
[/TR]
[TR]
[TD]Rhys[/TD]
[TD]Leg #11[/TD]
[TD]4.84[/TD]
[TD]44:07[/TD]
[TD]9:49 PM[/TD]
[TD]10:34 PM[/TD]
[TD]10:37 PM[/TD]
[TD]10:45[/TD]
[TD]02:53[/TD]
[TD]9:45 PM[/TD]
[TD]10:29 PM[/TD]
[/TR]
[TR]
[TD]Shawn[/TD]
[TD]Leg #12[/TD]
[TD]6.29[/TD]
[TD]61:13[/TD]
[TD]10:34 PM[/TD]
[TD]11:35 PM[/TD]
[TD]11:46 PM[/TD]
[TD]10:58[/TD]
[TD]10:40[/TD]
[TD]10:37 PM[/TD]
[TD]11:38 PM[/TD]
[/TR]
[TR]
[TD]Nicole[/TD]
[TD]Leg #13[/TD]
[TD]4.21[/TD]
[TD]42:40[/TD]
[TD]11:35 PM[/TD]
[TD]12:18 AM[/TD]
[TD]12:16 AM[/TD]
[TD]-34:55[/TD]
[TD]-02:00[/TD]
[TD]11:46 PM[/TD]
[TD]12:28 AM[/TD]
[/TR]
</tbody>[/TABLE]

 

[RonB1111]

Try:

=IF(L16>0,MOD(L16-L15,1)/H16,"")

If this doesn't yield the results you were expecting, please post your expected results.

 

[BilgeRatPDX]

Can you email me a copy of your excel worksheet? I am interested in seeing your solution.

Thanks

 

[Katinthehat]

I am trying to create a sheet that will allow me to estimate the time for each runner (12 total) over 36 total legs for the Hood to Coast relay in a couple weeks. I have the layout below with these colums

F-runners name
G-leg #
H-total miles for that leg
I-estimated time that leg should take based on their pace
J-estimated start time based on all runners hitting their goal pace
K-estimated finish time based on all runners hitting their goal pace
L-I will enter the actual time each runner finishes their leg
M-will calculate the pace for each leg base on the previous runners finish time and the current runners finish time
N-will show the difference in actual finish time for each leg and our estimate to track overall team time
O-will give a revised start time for each runner based on overall team time
P-will give a revised finish time for each runner based on overall team time

I have most of the functions working well and thought I was done until I start to enter some made up time and got to the point we go passed midnight and am now getting odd numbers and I cant seem to figure out how to fix it (see below) -34:55 per mile pace. The formula I use above this to determine the pace is =IF(L16>0,(L16-L15)/H16,"") which is saying- if I enter a finish time for that leg to take the finish time of the previous leg (which will be that start time of the current) and subtract it from the current leg finish time and then divide by the total miles in that leg. Let me know if you have any questions of would like me to send you the file. Thanks

Andrew

		Miles	Est time	Est Start Time	Est Finish Time	Actual Finish Time	Pace	Difference	Revised Start Time	Revised Finish Time
Nicole	Leg #1	5.64	57:09	1:15 PM	2:12 PM		-20:57
Stebs	Leg #2	5.67	54:20	2:12 PM	3:06 PM	3:06 PM	39:47	-00:29	6:52 PM	#N/A
Ryan	Leg #3	3.93	33:09	3:06 PM	3:39 PM	3:49 PM	10:56	09:22	3:06 PM	3:39 PM
Dom	Leg #4	7.18	70:36	3:39 PM	4:50 PM	4:45 PM	07:48	-05:14	3:49 PM	4:59 PM
Andrew	Leg #5	6.08	46:37	4:50 PM	5:36 PM	5:36 PM	08:23	-00:51	4:45 PM	5:31 PM
Tim	Leg #6	6.82	61:36	5:36 PM	6:38 PM	6:35 PM	08:39	-03:27	5:36 PM	6:37 PM
Steve	Leg #7	5.48	43:34	6:38 PM	7:22 PM	7:22 PM	08:35	-00:01	6:35 PM	7:18 PM
Cary	Leg #8	4.55	43:23	7:22 PM	8:05 PM	8:06 PM	09:40	00:36	7:22 PM	8:05 PM
Asa	Leg #9	6.91	53:54	8:05 PM	8:59 PM	8:58 PM	07:32	-01:18	8:06 PM	8:59 PM
Mel	Leg #10	5.12	50:41	8:59 PM	9:49 PM	9:45 PM	09:11	-04:59	8:58 PM	9:48 PM
Rhys	Leg #11	4.84	44:07	9:49 PM	10:34 PM	10:37 PM	10:45	02:53	9:45 PM	10:29 PM
Shawn	Leg #12	6.29	61:13	10:34 PM	11:35 PM	11:46 PM	10:58	10:40	10:37 PM	11:38 PM
Nicole	Leg #13	4.21	42:40	11:35 PM	12:18 AM	12:16 AM	-34:55	-02:00	11:46 PM	12:28 AM

<colgroup><col span="3"><col><col><col><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>

Hi Andrew! Would you be willing to share this doc? looks great and super helpful for H2C! How’d the team do?

 

[ajc623]

Hi Andrew! Would you be willing to share this doc? looks great and super helpful for H2C! How’d the team do?

Here is the google sheets files we used. It was pretty helpful to get an up to date picture of when things might happen. I also had found an excel file that someone had made years ago that would estimate your time for each leg based on your 10k time which seemed to be relatively accurate, can find it right now but I just found before on a google search. Let me know if there are any issues or you have questions. Have fun!

https://docs.google.com/spreadsheets/d/1rObHoTunnHD7Mygr6XJedtndf2wMv8MtPngrODW-yg8/edit?usp=sharing