Crazy formula to calculate total time in a running event

[camhall22]

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

 

[FormR]

One of these options maybe:

Excel Workbook
	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
Sheet1

 

[camhall22]

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>

Spreadsheet Formulas

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

 

[FormR]

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