Forecasting a marathon on the fly
Due to my role for this year’s ING NYC Marathon (about which more later) I decided I needed to be able to extract more information from marathon splits as the marathon is actually happening. I remembered that for previous Boston Marathons, David Monti of Race Results Weekly, doing a similar job to what I’m doing, had a fancy-dan Excel spreadsheet to take splits and project times.
While it’s possible that David added some kind of course factor to his spreadsheet, it seems more likely that he did what I did recently: he made projections based on simple math. For example, they’ve reached ten miles in time X, they have sixteen and two-tenths to run. If they run 16.2 at the pace they ran the last mile, they’ll finish in time Y; if they run it at their average pace for the last ten, they’ll finish in time Z.
I did this for mile splits and for 5km splits, which are the numbers I expect to get in New York. I added some conditional formatting to show me if the leaders were speeding up or slowing down, and if they’re ahead or behind course record pace, using colors. (I suppose if I was really a spreadsheet champion I could use varying shades to indicate how far they were from course record pace.) Adding sheets for the wheelchair athletes will also happen before race day.
I had 5km splits for Robert K. Cheruiyot’s Boston Marathon course-record run handy, so I plugged those in and it worked like a champ.
A sheet which would take a finish time and place and calculate prize money with time bonuses would be pretty cool too, I guess, but it’s not quite as algorithmic–it’s more ahead-of-time data entry. New York does have a deep time bonus structure, and the total prize money package will vary widely depending on how fast the pack runs.
If you have any other ideas of useful and/or interesting on-the-fly calculations, take a look at the sheets and let me know what to add. I did the original work in OpenOffice.org Calc, and exported to Excel, so I haven’t tested the Excel version. (Here’s the original .ods version.) You are, of course, welcome to use these yourself during whatever marathon you’re watching; you can see the appropriate cell to change to set the course records.