Predicting Finish Time for Running Races

Hello,

I was wondering if someone who is good at statistics or standard deviations maybe could help me out with a time prediction sheet. I want to have an educated guess of where someone may be at a finish of a race, based on their last known time. I tried averaging out a ratio of an intermediate time to the finish time, but due to the wide range of pacing, the predicted values I am coming up with aren't close at all.

Ideally, I think I need to incorporate a formula that will take the factors of the people near a runner and use that to calculate a projected finishing time. You will see in the attached spreadsheet that there are a few people with #N/A for their true finish. My goal is to try to identify where these people may have finished and just not recorded a time.

Ideally, I could get within a 30 second range, but maybe this is all unrealistic...

Part of the approach depends on what you will do with your prediction once you have it, and therefore how reliable/accurate it needs to be.

If you are using only one last known time, you have no hope of anything reliable.

If you have a longer history and wanted to do it statistically you would probably predict a range of finish times (not a single time) and assign a confidence level for that range. The more confidence you need in your prediction, the wider the range gets.

You could do a linear regression, which assumes a trend over time in the runner's performance. I don't know if that reflects reality of athletic performance.

You could also do some kind of rolling average, which would be coarser than the above (i.e., average the last five races).

One problem with this whole idea is that there are variables involved besides just the runner's past performance. There are slow races and fast races depending on who is competing and the track and weather conditions. There is the fitness level at race time. There is the mental state at race time. If you could predict a runner's time reliably based only on their past races, then someone would have made a fortune by now at the horse races.

What are FS and "True Finish"? Are the distances always the same?

Are you trying to predict finish time once they reach the split point? Or before the race starts?

endtime = 4,2293 * time first seen

I made real times for your data in columns K:L.
Because of those 7 runners without endtime, I sorted the list so that those runners are at the end of the list.
In P2:Q4, there is a calculation of a linear regression between "time first seen" and "finsh time" for the runners who arrived.
That factor is 4.23 (also see graph in other sheet)
Column M is the estimated time of finish with the "time first seen" multiplied with that factor.
Column N is the difference between the real time and the calculated time (is text because neg. times give errors !)
M98:M105 give the times for your "runners without valid endtime".

Now you can make a 2nd approach, the quick starters in the first lap are the ones < 6:10
Their finishtime is factor 4.16.
For the slower starters, the factor is 4.32. (4% slower)
see P5:Q8
Perhaps, this is too much detailed and not included in the question.