Skip to main content

I just got through submitting a half marathon course with segments included from 5 measurement dates over 4 years. My primary certifier justifiably requests a spreadsheet when I have to do stuff like that. Everything in my BC & CM pdf's was copied and pasted out of an xls. I made frequent mistakes before I started doing it that way.

I know it has been discussed before but here in 2017: How close are we to changing over to spreadsheet submissions for courses? How many of the certifiers reading this still actually go through the line by line information in the BC & CM forms? Why can't we just put all that and the App in one spreadsheet? It self checks to a large extent and it ought to save everybody a ton of time.
Original Post

Replies sorted oldest to newest

I have a excel spreadsheet for all the races I send to Mike Wickiser. I have the cells color coded so he knows what is entered manually and what is generated by excel. I only have a 5k made up and if I do a longer race I just add to it. If anyone would like to see it I would be more than happy to send it to you just send me an email.
I have everything in it. The pre and post calibration, course measurement & application for certification.
JA, that sound promising, I'll send you an email to request that one. You may be addressing the problem I was thinking about in response to Oscar's post: I have seen a lot of spreadsheets that make a lot of sense to the measurer (I presume!) but are not completely clear to the certifier. And some spreadsheets even get populated with misleading information-- e.g. showing the kilometer splits as if the measurer stopped at them when in fact they didn't.

I guess a more general question to ask is: does a spreadsheet convey a false sense of authority? I am undecided on this, but I am a little leery of too much "automatic" thinking.

Thinking of a case like Oscar describes underscores for me the importance of that "essay question" that we recently added to the application.
As a certifier, I do go through the map and every line on the application. Several measurers send me their numbers in a spreadsheet. I do not rely on one spreadsheet alone. Spreadsheet formulas are editable and may or may not be correct.

I use Bob Baumel's measurement program to check the math for each application. The older DOS measurement program works well on any PC and will produce a measurement check report like the one in the link below.

Example Calculation Check Report

The DOS measurement program does require a DOS emulator in order to run on newer Microsoft operating systems. I use DOSBOX on my Windows 7 and Window 10 computers.

Bob's DOS Meaureurement is available on the USATF website. A copy is available at the link below.

DOS Measurement Program

Thank you. -- Justin
Last edited by justinkuo
Oscar and Mark,

Counts may be read to an accuracy of 1/2 count. The DOS measurement program allows you to enter counts with any decimal value. However, it will round the counts to the nearest 1/2 count. (i.e. 10000.2 rounds to 10000, 10000.3 rounds to 10000.5, 10000.8 rounds to 10001, etc.) The leftmost column is for display and is not used in the constant calculations.

In the sample report, the text, through the line that starts with "(Sum of Shortest Splits = " was generated by the DOS measurement program. I added the text below, which should have said "shorten the course by 0.06 meters."

The measurer calculated the course as "9.3206 miles" (rather than 15000.00 meters.) Since this was a first time measurer, I did not insist the course be calculated with metric values, or that he calculate to nearest 1/10000 of a mile. I allowed for rounding errors of 0.00005 miles or 0.08 meters.

Thank you. -- Justin
What I am seeing is that:
1. Bob always checks the standard forms and there were no other responses from certifiers.
2. Justin provided an example of how a home made computer program can:
a. lack intuitiveness as evidenced by my question
b. has the potential for unforced errors as evidenced by Mark's question
c. is going to be tricky to use universally as evidenced by how confused I was after reading about how to (safely) install and use DOSBOX.

The system could very easily be converted to a standard Excel spreadsheet (or Open Office if there is a commercial use issue). This would allow (force the "thinking through of) standardizing such things as digits of precision required at every step or what was done to adjust course distance to the desired distance.

I love the State of Franklin Track Club's "automagical" Event Calendar because it forces race directors to think through things they commonly ignore. It (largely) saves me as a timer from unnecessary awards errors.

Once you have forms converted to a tightly macro'd spreadsheet, the only thing transferring to the pdf's can do is cause the poor certifier more nuisance errors to straighten out with a careless or poorly informed measurer. Maybe it would even force the measuring community to once and for all to resolve / standardize the "sum of the shortest segments" issue. I still have a bee in my bonnet for having been sent back to the first course I ever measured commercially for something that is not in the manual.

Justin, if you have that DOS source code in something like C or Basic, I will be happy to rewrite it in directly executable Windows code. However, I think the conversion to a thoroughly "thought through" spreadsheet would significantly simplify the certifier's task.
Not sure what you mean exactly by going through an application "line-by-line." As a certifier I repeat every calculation that the measurer had to go through that affected the length of the course. I don't recalculate the number of counts for each mile split, although I do take a look at those to see if there are inconsistencies, especially if the final numbers weren't so good.

I agree with you 100% that we should have a standardized spreadsheet version of the application. But I think we need to be very careful about what we put out there. Protections would be to be included so the measurer could not change anything, and only input data. Standardization doesn't help if the certifier can't be sure the measurer didn't change a formula. So we would need to be careful, but I think it could be done with a reasonable amount of effort for the case when the measurement is a straight-forward, start-to-finish (or vice-versa) measurement. Including the possibility of "sum of segments" measurement would definitely be version 2 (at least).
Just for the sake of clarity of expectations for measurers, an approved spreadsheet would be a big help. If we want to stick with copy to pdf after that, oh, all right. But something reviewed and approved, like A.J. Wilhelm's excellent set up, should cut down on careless errors, misunderstandings and omissions if nothing else.
Surely we are beyond the time when there is anybody out there with the smarts to measure who can't run a spreadsheet.
Jim's spreadsheet works great. I do go over it and check the formulas but in several years, the data has always been spot on. I check the formulas and then check final adjustments.
The process takes maybe 5 minutes.
Jim also draws some great maps.
I usually spend more time formatting the pdf and png files AND correcting my transcription errors than actually reviewing Jim's measurement work.
This is an interesting discussion. Yes, it is time to have a universal spreadsheet for the calculations. I have seen several measures use ones they developed.

I would hope our Certifiers go over the numbers and application. I know our application for certification has changed to make it easier for the Certifier to fill in the certificate. That is the present applications order matches the lines to fill in on the certificate.

Even if there is a specific PDF/excel form for the numbers, one still has to do the numbers while measuring. However, maybe they have this information built into a device to get all the numbers.

Lastly, this reminds me of how things have changed. Go to a store and see if the cashier can give you change without their register doing it. I was a HS teacher and over the years I saw many changes with our students using calculators that did all for them. As AP Calculus teacher, on the AP test there was a section that one had to show their work without a calculator. I really don't like that we keep making things easier.
Justin, if you have that DOS source code in something like C or Basic, I will be happy to rewrite it in directly executable Windows code. However, I think the conversion to a thoroughly "thought through" spreadsheet would significantly simplify the certifier's task.

Oscar, the DOS Measurement program, has been around for a long time. I have been using it since 1990. The program was written by Bob Baumel. I believe the original package included instructions. We'll need to ask Bob it he has the original source code. I like the DOS Measurement because it was carefully thought out, it's simple to use, and cannot be broken or accidentally modified.

It is possible to create a similar program in another, more modern, programming language. I believe the spreadsheet would be a good place for experimenting, but, ultimately, should be coded as stand-alone application that can be used on a desktop computer as well as mobile devices.

Thank you. -- Justin

A stand alone would allay concerns about tinkering with formulas somewhat:

Ãÿÿ ŒØEU‹ìŽØVW‹V¾K­;Ât@–t —3À¹ÿÿò®‹ (from a DLL)

I would be just about as happy to see offered a program which would allow, with the spacing and significant figures carefully (painfully?) thought out, that would at least allow almost mindless copy and paste to the existing forms. Ihen the certifier could copy and paste to his own program for a final error and tinkering check. Or it could produce a text file for those purposes.
Yes Oscar I think what you say is the way to do it. I think below is what you are talking about.

Give measurers a spreadsheet where they input their data. This spreadsheet does all the calculations for the measurer. The measurer sends this spreadsheet to the certifier.

The certifier has his own spreadsheet that has a function to copy the data (and only the data) from the measurer's spreadsheet to the certifier's version of the spreadsheet (which he knows has not been fiddled with). The certifier's version of the spreadsheet also does all the necessary calculations on the data.

This is a way to avoid the problem of the certifier having to trust the formulas in the spreadsheet the measurer has sent him. The formulas in the measurer's spreadsheet are never used by the certifier.

Add Reply

Link copied to your clipboard.