Crazy formula to calculate total time in a running event

camhall22

New Member
Hi,

Im trying to make an ambitious formula which will calculate the total time (hh:mm:ss) to complete a marathon (42km) based on the pace per kilometer.

Easy right? Only problem is Im basing the formula around drink stations which are are irregular intervals (see attached).

Column A (total distance)

5.14km
7.5km
10.0km
12.75km

Column B (pace per km in mm:ss)

5m 30s
5m 30s
5m 45s
5m 20s

Column (total time)

= 5m 30 per km x 5.14km
= 5m 30 per km x 7.5km
etc etc

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
One of these options maybe:

Excel Workbook
ABC
1KMPaceTotal
25.1400:05:3000:28:16
37.500:05:3000:41:15
41000:05:4500:57:30
512.7500:05:2001:08:00
63:15:01
7
83:15:01
Sheet1

Thanks for the reply, I think this can be worked with to do what I intend.

Only problem is Excel keeps changing the time from 00:05:30 as an actual TIME (as in 5:30pm) - how do you stop this? I want elapsed time like a stopwatch

One of these options maybe:

Sheet1

 A B C 1 KM Pace Total 2 5.14 00:05:30 00:28:16 3 7.5 00:05:30 00:41:15 4 10 00:05:45 00:57:30 5 12.75 00:05:20 01:08:00 6 3:15:01 7 8 3:15:01

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:66px;"><col style="width:69px;"><col style="width:68px;"></colgroup><tbody>
</tbody>

 Cell Formula C2 =B2*A2 C6 =SUM(C2:C5) C8 =SUMPRODUCT(A2:A5,B2:B5)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Only problem is Excel keeps changing the time from 00:05:30 as an actual TIME (as in 5:30pm) - how do you stop this?

You can custom format the cells to "hh:mm:ss" - note, when entering times in minutes and seconds you need to include the hours i.e. to enter 5 minutes and 30 seconds enter as 00:05:30

Replies
9
Views
528
Replies
4
Views
187
Replies
1
Views
1K
Replies
2
Views
904
Replies
5
Views
506

1,207,089
Messages
6,076,518
Members
446,211
Latest member
b306750

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back