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

[Rhothgar]

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!

 

[AlanY]

have a look if this works for you

Book1
	A	B	C	D
1	Distance	10	miles
2
3	Start	Time	Average Speed (mph)	Place
4	11:37:33	12:06:33	20.7	2
5	10:35:38	11:05:38	20.0	3
6	12:58:11	13:43:11	13.3	8
7	DNS	12:57:33	---	---
8	14:10:11	14:43:11	18.2	5
9	14:05:52	14:37:52	18.8	4
10	14:34:40	14:58:40	25.0	1
11	DNF	17:12:31	---	---
12	17:15:57	17:58:57	14.0	7
13	14:59:09	15:35:09	16.7	6
Sheet1

Cell Formulas
Range	Formula
C4	=IFERROR($B$1/(B4-A4)/24,"---")
D4	=IFERROR(RANK.EQ(C4,$C$4:$C$13),"---")