Crazy formula to calculate total time in a running event

camhall22

New Member
Joined
Feb 2, 2012
Messages
19
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,483
Office Version
  1. 365
Platform
  1. Windows
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
 

camhall22

New Member
Joined
Feb 2, 2012
Messages
19
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

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
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
CellFormula
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,483
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top