Flat Hills Road

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.

Last fall, I wrote a piece for New England Runner about the changing face of transponder (aka “chip”) timing, where long-dominant ChampionChip was seeing a new wave of competition from lighter, cheaper, and sometimes “disposable” new technology.

Even since publication, however, the chip world has moved on from the state of last fall. I’m told that ChampionChip is nearly out of the picture (you’ll still see them around, as timing companies which own ChampionChip equipment will keep using it) and the disposable chips are dominating. I haven’t heard if the waste issues raised by the disposables have been addressed, nor do I know if the “holy grail” of chip timing, a transponder which can be embedded in the bib number and worn without any extra work from the runner, is any closer.

With that said, here’s where things stood last September. This is as I submitted it, not as it eventually ran, so there may be errors and issues; bear in mind that “this year” means 2008. Hyperlinks, rather than coming up in the text, are all provided at the end of the story.

(more…)