Using a spreadsheet on a palm computer

Contributions like this will make plug-pulling more difficult. Keep it up!

I'd love to have the spreadsheet sent to me.

I started using a spreadsheet on the Palm pilot several years ago. It helps me avoid math errors, transposing digits, and may be printed as soon as I sync with my desktop PC. I exchanged spreadsheets with Rick Schumeyer and incorporated some of his ideas into mine.

My latest version is available online at:
Measurement Templates

Let me know if these are useful or if they should be modified.

Enjoy. -- Justin

The use of electronic counters with their instant zeroing capability instead of the Jones drastically reduces the number of required readings and calculations, so that spreadsheets are quite unnecessary. For instance, to locate all mile splits on a course I just repeatedly use the calibration constant expressed as rev/mile with zeroing before each split.
The use of the tire-pressure method I am developing will further simplify calculations by eliminating postcalibration measurement and course adjustment that can be quite annoying.

I don't have a palm pilot, but I looked at Justin's program. It seems to have all that anyone could want for a pre-planned measurement.

My measurements are rarely pre-planned to a point where I get two complete and uninterrupted rides from one end to the other. My last measurement went like this:

Arrived 40 minutes early for a meeting with the race director to show me the location of the finish line. Since I knew the basic circuit I did a loop ride beginning and ending at a reference point.

When the RD arrived, I measured the distance from the reference point to the finish line and found that I needed to remove 11 meters from the course. I did this at the reference point. Now I had completed one measurement of all the bits and pieces.

Then I did a standard start-to-finish ride, laying out the splits as I went.

A small final adjustment (second ride was shorter) finished the job.

I would not have been able to use a pre-planned program for this job.

I've also found that it is wasteful for me to calculate all the splits in a marathon measurement ahead of time. I generally calculate three or four splits ahead, and recalculate when I arrive at the last calculated point. I sometimes get off-course or otherwise screw up my plans.

When I am all done my final calculations look much like Justin's. For an example see:

SampleMeasurement.pdf

I guess I'm old fashioned and should start measuring with the electronic counters.

In the meantime, while measuring with the JO counter, I will still need a sharp pencil, paper and a calculator. The Palm handheld device, with it's spreadsheet, replaced my calculator. While the spreadsheet templates that I posted may be used on a desktop computer, they are most useful in the field on a Palm handheld device where I can enter the data and make adjustments to the calculations. (I use Quicksheet on the Palm. Cell formulas and data are easily edited on the handheld. )

All course measurements are unique. However, some calculations are routine and may be automated. For example, while a very simple calculation, calculating the working constant and the constant for the day from the pre and post cal rides is quickly performed on the handheld spreadsheet.

Calculating the splits is a simple routine calculation as well that is easily performed on a handheld spreadsheet.

The course adjustment is the least likely to fit a standard spreadsheet template. For clarity, I set up the adjustment template from the example in the Measurement Manual. I reality, my measurement rides are more like Pete's example. That is, my first ride is from start to finish without taking mile splits. I'll adjust the start and finish as necessary, and then make a second ride, locating mile splits along the way.

If I can, I'll pre-plan the course and modify the adjustment spreadsheet at home. In the field, if the template does not fit, I can make simple modifications like adding or skipping split points.

If the course is complex, I'll use the paper, pencil and calculator and check them on the spreadsheet after I return home.

I separated the measurement tasks into three spreadsheet templates. One template is for pre and post calibrations. Another template is for calculating splits and third is for calculating the course adjustment. The user may select to use and modify any of the three templates. A new user may want to start with just the calibration and splits templates.

My handheld spreadsheets templates do not fit every situation or user but it's my hope that they may be developed into tool to make the measurers task a little bit easier. -- Justin

Justin:
The time you spend in setting up for measurement with the Protege will be recouped within the first one or two measurements that you can make without using the Jones, so it does not make sense to delay the switch. Besides if you wait too long the Protege may become unavailable and your only option may be the Jones.(The consulation will be that your time developing the spreadsheets will not have been wasted!)

I believe you and Pete are following the Measurement Manual and making final course adjustment by converting counts or revolutions to standard units such as meters and measuring with a steel rule. Recently in MN I described a much simpler and more elegant method that avoids this conversion and measurement with a steel rule.

For instance with a fixed start and variable finish, the measurer simply selects the line at the finish of the ride that gives the longest course as the certified finish. The only calculation (in cts or revs) necessary is that for the pecentage difference between the two course measurements using the cts or revs found during the second ride for the first finish. For a fixed start and finish the measurer simply adjusts the U-turn using bicycle measurement.

Note that, although not intuitively obvious to most measurers, the above course adjustments in cts or revs are valid even if there is a diffence in the second ride with the rider, bicycle, day, or temperature.

Neville has re-invented the wheel.

The “book” method of making a final adjustment arose about 20 years ago. At that time people were making two sets of marks as they measured. There is nothing inherently wrong with this, but when the data was sent to the certifier, the question arose “How far apart were the marks when the measurement was completed?” Generally no satisfactory answer was provided, as the measurers rarely recorded the difference. Thus no way of knowing the difference between two measurements was possible.

If this is done, it is necessary to take a reading at the previously-established end point so as to secure a solid second reading. Then one may safely go on to whatever final point one wishes.

It is much clearer to explain when two measurements of the same thing are made, and a final adjustment based on that is done.

Also, when three or more measurements are made, confusion reigns. I remember looking at data from a long-ago New York City Marathon measurement. An adjustment was to be made based on the position of the 18 mile mark. However, six riders had measured the course and there were 18 mile marks all over the place. A clear understanding of what had been done was just about impossible.

Pete:
Since the course adjustment method I am advocating was appreciated twenty years ago, rediscovery is long overdue. If the problem was only that measurers were not getting a second reading on the first measurement, why was this not fixed simply by listing this as a requirement on the submission form? How did we end up with the present embarrassingly clumsy method?

According to the Manual, the measurer on the second ride must stop at the first finish point if it obvious that the second ride is going to produce a longer course. He must then take out his calculator and make several calculations including the conversion of counts to meters, meters to feet, and feet to feet plus inches. Finally he must take his rule and measure the extension of the course from the first finish to the certified finish. In contrast, with the method I advocate the certified finish is always instantly determined at the end of the second ride without calculator or measuring rule.

I am not sure I grasp the significance of your story about the 18-mile split, but it sounds like a special case the problem of which could have been avoided if measurers had used different colored markers.

The old two-sets-of-marks method worked just fine for those who did the actual course layout. It was the certifiers who got confused.

Early on, during the transition period from two to one set of marks, I would get applications lacking the separation of the two marks. This was not always at the end of the measurement. Data for the individual splits was also submitted that way. This got confusing sometimes, because the separation would sometimes seesaw – that is, the two marks for a given split might be separated in a different direction from split to split.

The problem came about because people did not read the instructions. They used the forms, designed for one set of marks, and submitted data for two sets. It was like pulling teeth to get complete information, because they did not write it down and had forgotten.

Although the instructions say to use a steel tape for a final adjustment, this is widely ignored. I know of nobody who always does it. The instructions should be changed. The method Neville proposes for laying out a finish line is just fine – so long as the data is submitted in an understandable manner.

An application for certification is like a surveyor’s report – it must be understandable to the reader. It is not enough for the measurer to know what he is doing – he must convey his methodology in a clear manner to the reader.

The present forms work well for one set of marks. I know of no forms that outline how data should be presented for two sets of marks.

Pete:
I must confess I have not thought much about double lines for uncertified splits as I only ever have made one determination on them. I feel confident about their location if I get a very small percentage difference between the determinations on the overall course. The change to the requirement that no new lines should be made on the second ride was obviously a good move except that it went just a little too far. It should have specified that if the second ride was going to produce a longer overall course, a mark showing this should be placed beyond the first set of marks. Without the need for calculation of a course adjustment, the “Course Measurement Data Sheet” could thus have been made much simpler and clearer.

You tell me that most measurers are ignoring the recommended procedure to make course adjustments with a steel rule, so evidently they are following the same methods that I am advocating. At variance with this though is the fact that Justin’s latest spreadsheet gives a result for course adjustment in meters. Justin are you making adjustments with a steel rule?

Although general course adjustments currently approved by RRTC involve unnecessary conversions to standard units and use of a steel rule, there are some occasions where this is necessary. For instance a measurer may find he needs an adjustment after completion of a post-calibration at a point distance from the race course, and may have to ask the race director to do it. However, use of the tire-pressure monitoring method I am developing would eliminate post-calibration and the need for even these adjustments.

One set of marks, two sets of numbers, actual distance since that allows easy comparison if multiple days or riders are involved --- that's the system that evolved out of a lot of confusion and anguish.

I actually prefer that the "marks" where readings are recorded be landmarks so that the certifier can check to see that the distances from landmarks to the start, finish, turnaround points, and splits are accurate. David Reik, West Hartford, CT

David:
Do I interpret your post correctly that you agree that the measurer should place an appropriate mark on the second ride if he finds a longer course?

quote:

Originally posted by Neville:
David:
Do I interpret your post correctly that you agree that the measurer should place an appropriate mark on the second ride if he finds a longer course?

I think the second mark would be O.K. as long as you also recorded a reading at the same point you recorded a reading on the first ride. That point doesn't need to be either mark, but could be a landmark (lightpole, catchbasin or the like), near the marks, which will be used on the course map to descibe the location of a course point.

I was a certifier for 15+ years, and as Peter suggests, things could get very confusing if the measurer did not record and report readings at the same set of marks for both rides. Things got particularly confusing when the rider had to make last-minute changes to the course and was trying to tack on a newly measured stretch to what he had previously measured. The certifier has to answer, on the certificate, the question, "Difference between the two best measurements of the course." That question can be impossible to answer if the measurer has not recorded two sets of readings at one string of marks.

David Reik, West Hartford, CT

I will follow up on this thread under Course Measurement -- Elimination of Course Adjustments.

quote:

Originally posted by Justin Kuo:
I started using a spreadsheet on the Palm pilot several years ago. It helps me avoid math errors, transposing digits, and may be printed as soon as I sync with my desktop PC. I exchanged spreadsheets with Rick Schumeyer and incorporated some of his ideas into mine.

My latest version is available online at:
Measurement Templates

Let me know if these are useful or if they should be modified.

Enjoy. -- Justin

I find the spreadsheets extremely useful, but can't get them to work on my Palm. I keep getting a message saying some functions are not supported (using Excel in Docs to Go). I'm therefore obliged to take my laptop when measuring. Any suggestions on using the templates on the Palm would be greatly appreciated. It would be SO much more convenient. Thanks.

Mike

I'm wondering if Justin's templates can be uploaded as GoogleDocs. Then all you'd need is internet access, either via WiFi or cel service, to access them through your smartphone's browser.

The calcs are in Google Docs. I'll find a way to make them available when I return from a brief vacation in Maine. Thank you. -- Justin