Calculating Average Speed from hh:mm:ss and known distance

Rhothgar

Board Regular
Joined
Sep 24, 2013
Messages
51
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Afternoon,

Hope someone can decipher this code because I've looked and looked at it and it is WAY beyond my capabilities.

I was kindly given the spreadsheet by another organiser but would like to refine it slightly.

It's for a time trial.

Basically, the event distance is entered at the top and the equations work out the rider's average speed and position in the field and places zeros where they "DNS" (Did Not Start) or "DNF" (Did Not Finish). DNS would /should be put under the Start Time and DNF under the Finish Time. This could be altered to allow DNS or DNF both under the Start Time as Finish Time does not need to be displayed.

At the moment the spreadsheet uses their start time time and the end time BUT the timekeeper does not relay the end times of the rider, they relay the actual time taken to cover the distance.

I would like to re-jig to show their Start Time but not their Finish Time so all that would need to be entered is the actual time and it should calculate their average speed and position on the 2nd worksheet.

Picture of first sheet layout (Worksheet: Start Sheet ITT)

https://imgshare.io/image/ZOvgY

Equation in F6 is thus:-

=IF(B6="","",IF(E6="",0,IF(E6="dnf",0,IF(E6-D6<0,"",E6-D6))))

Equation in G6 is thus:-

=IF(F6=0,0+ROW()/100000,IF(F6="","",($B$4/F6)/24+ROW()/100000))

Where $B$4 is the distance as an integer, ie. '10' or '25' (miles)

The Original Results Worksheet looks like this:-

https://imgshare.io/image/ZOmRn

Equation B1:-

=IF('Start Sheet ITT'!G6="","",IF(E10=E9,B9,'Start Sheet ITT'!A6))

Equation C1:-

=IF(B10="","",INDEX('Start Sheet ITT'!B$6:B$129,MATCH(LARGE('Start Sheet ITT'!G$6:G$129,'Start Sheet ITT'!A6),'Start Sheet ITT'!G$6:G$129,0)))

Equation D1:-

=IF(C10="","",VLOOKUP(C10,'Start Sheet ITT'!$B$6:$C$129,2,FALSE))

Equation E1:-

=IF('Start Sheet ITT'!G6="","",INDEX('Start Sheet ITT'!F$6:F$129,MATCH(LARGE('Start Sheet ITT'!G$6:G$129,'Start Sheet ITT'!A6),'Start Sheet ITT'!G$6:G$129,0)))

Equation F1:-

=IF(B10="","",IF(LARGE('Start Sheet ITT'!$G$6:$G$129,'Start Sheet ITT'!A6)<0.1,"DNF",LARGE('Start Sheet ITT'!$G$6:$G$129,'Start Sheet ITT'!A6)))

It looks very top heavy but I'm guessing it needs to be to work out rider positions.

I think the only thing that might need altering is the worksheet, "Start Sheet ITT" to only entry of the actual time and not have the scoreboard assistance having to work out what time the rider actually finished the course!

Any help greatly appreciated!

Event is tomorrow!
 
Last edited:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
have a look if this works for you


Book1
ABCD
1Distance10miles
2
3StartTimeAverage Speed (mph)Place
411:37:3312:06:3320.72
510:35:3811:05:3820.03
612:58:1113:43:1113.38
7DNS12:57:33------
814:10:1114:43:1118.25
914:05:5214:37:5218.84
1014:34:4014:58:4025.01
11DNF17:12:31------
1217:15:5717:58:5714.07
1314:59:0915:35:0916.76
Sheet1
Cell Formulas
RangeFormula
C4=IFERROR($B$1/(B4-A4)/24,"---")
D4=IFERROR(RANK.EQ(C4,$C$4:$C$13),"---")
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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
Back
Top