STIRLING MARBLEHEAD YACHT RACING CLUB Inc.

Woorabinda Lake - Stirling South Australia

  Home  History  Location  Photos  Results  For Sale  Suppliers  Sail Making  Sailing Hints  Weather  Batteries  Links  Membership
Excel Handicapping Program for Radio Yachting Events

Quick Find:

 Download Files:    Right-click on the links and select 'Save Link As' to save file on your computer

Program File currently Version 2.4 - Check your version!          Count-up File

Preface

This program has been written as it was seen by some participants in handicap events that it was too difficult for an individual PRO or handicapper to keep a handle on a dozen or more yachts and make adjustments to their handicaps manually.  Trevor Jeffree did an initial concept design in Microsoft excel which seemed to work simply by recording the differences between the finishing times of the yachts in a race.  Ben Morris then agreed to simplify the mechanics of the process making it easier to use and be able to be run by one (preferable two) persons.  It has undergone considerable development and has been used successfully for SARYA Interclub and club handicap events.

Goto Top

Introduction

The program was initially designed for handicap races between clubs as a team event and still retains that option though it works equally well within a club for their handicap events.  It is designed to run on standard Microsoft Excel software on a PC.  It is written in version of office 2003 so should work fine in all subsequent versions. There may be some issues trying to run it on the later Apple version of office on an Apple computer.

The main reason behind the production of the program was to allow sailors/yachts of unequal ability to compete together and allow all yachts to have an equal chance of winning overall.

The races use a staggered start so slowest yachts start first and faster yachts begin later.  This is achieved using a ‘count-up’ timer. 

In this way all yachts get the opportunity to actually make it to the finishing line first something the slower yachts never experience in a standard scratch race.

The starting times (handicap starts) are generated by an excel program after each race and relayed to the competitors.  All times are rounded off to the nearest 5 seconds.

The number of races is currently limited to 12 (but could be expanded I suppose) and the number of competitors to 25 which is more than enough radio yachts to be on the water in a race at the once.

Goto Top

 

Setting up the event

The program file is best placed in a separate folder with an appropriate name such as ‘Handicap Results’ etc.  The ‘Master’ file is opened and data entered into it as described on the first sheet in the program and includes listing the names of the sailors, their sail number (as a number!) and either a club or class of yacht in the third column (Click on heading to change it from club <> class.  A name for the event and the date of the event are required to be typed at the top of the sheet and the ‘Save/Save as’ button pressed.  This saves the file with a new name and preserves the ‘Master’ program in its empty state for the next race.

It is critical that your excel program is set to ‘Allow all content’ or ‘Allows Macros’ as most of the processing that occurs in the file is done in VBA (visual basic for applications).

It is also critical to remember in excel that if you change the content of a cell e.g. the one for the event name or the one for the date or one for the sailors or sail Nos or later for times etc then the information IS NOT committed or saved to the spreadsheet until you leave the cell and move to another cell so get into the habit of either clicking in another cell or pressing the ‘Tab’ key or pressing the ‘Enter’ key to move from the current cell.  Moving with the mouse to an action button directly from an edited cell and clicking on the action button WILL NOT WORK!  Excel is waiting for the edited cell to be accepted by moving to another cell first.

Writing the names of the competitors in a standard way will make the display on screen and in the printed results look better.  I write the surname first in block capitals and a given name of preferred name in lower case eg MORRIS Ben or JEFFREE Trevor.  Whatever you use keep it consistent. One of the options is to order by names.  The result will depend how you have written the names as it arranges them in alphabetical order so the first name written is the one it will sort by.

It is also critical that sail numbers are NOT ALTERED once the process starts as the program will falter as it tries to find information that is no longer there so GET IT RIGHT from the start!  Names of sailors are not so critical so they can be altered at any time and not interfere with the program.

Goto Top

 

How it works

Handicaps for the next race are based on the differences between the finishing times of the yachts.  The handicap is averaged over the last two races.

The first two races are used to generate a handicap (or vary an Initial Handicap) for subsequent races though they are both still counted in the results.  The first race is a scratch race where as expected the faster yachts finish first and the slower ones later.  The finishing times are recorded on the timing sheet by pressing (single click) on the button corresponding to the sail number of the finishing yacht as it crosses the line.  Finishing times will be recorded to 1/10th sec accuracy and then rounded to the nearest 5 sec.  The sail number on the yacht button will change to red.  Meanwhile the counter on the screen will have started.  Once all yachts have crossed the line and their button clicked, the counter will stop and the handicaps for the next race are calculated by pressing the ‘Set/Reset Handicaps for next Race’ button.  The list will be displayed and the times for the starts need to be relayed to the skippers.  I use a paper sheet listing all yachts in Sail number order to write the times down which is then available for perusal.  This is probably easier than trying to have skippers huddling around a computer screen or calling out the times.  See what works for you.

The handicaps for the second race are deliberately set to double what might be expected from the time differences in the first race.  This allows for the slower yachts to finish first and the faster ones to come in last so balancing the results from the first race.  The ‘real’ handicap times for the first race and a reworked set of times from the second race found by subtracting the starting times are averaged for race 3.  These values should be roughly ½ the values found from the first race.  Why use two races and not just the first as a starting point?  Well skippers tend to work the system and try to outguess the handicapper.  This process goes some way to eliminating that effect.  Even if a fast boat tanks in the first race and so does better in the second than might be expected, their handicap for the third will reflect this and so compensate!  There are no drops built into the scoring of the final results so they have to carry the results for those next few races where their handicap will put them further back in the field.  In addition there is an algorithm built in to generate a maximum time for a race which precludes a very late finisher from getting too large a handicap change by limiting its finishing time in the calculations to this maximum time.  It works! The use of an Initial Handicap even if only close to expected final value reduces the variations in handicaps derived from first two races but ends up with the same result.

From race 3 onward, the handicaps will stabilise and move a little up or down depending on how the yacht went in the preceding races as they are then averaged over the preceding two races with emphasis on the preceding race in the ratio 2:1.  This has the mathematical effect of averaging the handicaps for the last three races. 

It’s not quite as simple as that as there is some attempt to keep the handicap start times at a reasonable figure so movements of handicaps are biased towards the average handicap.  Even so the relationship between yachts is maintained.  In addition, if the starting times for the slowest yacht gets too small a correction is made to ALL yachts moving them all up say 30 secs and conversely if starting time for the slowest yacht gets too large then all handicap times are reduced by a number of seconds to bring the first yacht start to about 30 sec.  There is a warning given when this happens and it’s recorded on the timing sheet as an adjustment.  Letting the skippers know this has happened is a good idea!  Relationships between yachts are not affected.

As soon as all yachts have finished and new handicaps calculated, clicking on the next race from the list on the timing screen will set up the next race.

It’s a good idea to save the file after each race!

At any time you can view the results of the racing by clicking the ‘Show Results’ button and certainly at the end of racing this can be done.  There is even a button on the results sheet to display the results in a separate tidy spreadsheet in a separate file which is in an effective format for adding to a web page or club newsletter.

Goto Top

 

What if you get a time wrong for a particular yacht?

If the wrong yacht number button is clicked going over the line or the button is clicked too early then simply make a note of when later it actually does finish ie look at the timer on the timer screen and simply replace the finish time with the correct time.  As all times are rounded to the nearest 5 secs there is no need to record to more accuracy then the nearest second.  The list will automatically readjust itself to put the finishing yachts in the correct order.  Both the list of yacht Nos and Times for finishing on the timing sheet can be manually corrected - just be careful!!  If there are two people recording finishes it's a good idea to have one calling and perhaps writing down the finishing order while the other operates excel.  That way if a mistake is made then the times/Yachts can be fixed.  When doing this alone, I record a sound track on my smart phone of me saying when the yachts are approaching the finish and when they cross.  It is then possible to replay that and verify the excel values.

Goto Top

 

What about a draw or tie?

No matter how quickly you press the buttons two successive finishers will always be at least 1/10th sec apart.  Simply manually adjust the time of the second of the two yachts who draw to make it exactly the same as the first of the two yachts.  This will make them equal in placing, leaving subsequent yachts correct.  Will it handle a three way tie??  No idea!  - just tried it and yes it does handle it correctly!

Goto Top

 

What about DNS, DNF or DSQ etc results where a skipper does not finish or doesn’t start?

If a yacht doesn’t start or finish then once all other yachts are clicked and a time recorded then either of the two steps below is performed

1.       the DNS or DNF yacht is clicked and a time recorded for it.  The time recorded is replaced by the typing over the time the characters ‘DNS’ or ‘DNF’.

2.       the yacht number of the yacht is typed into the yacht No column under the last recorded yacht and ‘DNS’ or ‘DNF’ typed into the time column.

This is repeated until all yachts have some record in the timing section and all yacht numbers on their button has changed too red and the timer stopped.  You may see a ‘*’ symbol appear in the column headed ‘Base HCap for next race’.  As well the yacht position will changed to the No of yachts plus one.

When the handicaps are processed for either, the yacht will get an average handicap for that race.   This has the effect of simply retaining the handicap the yacht had in the previous race.

Goto Top

 

What about redress?

If a yacht is interfered with and severely disadvantaged by another yacht and both are in agreement about blame etc then the easiest way to deal with this is to manually adjust the finishing time for the yacht to give them it an average finish time for the race.  The average time is displayed at the bottom of the finishing time column and can be manually added in their time (replacing a DNF etc).  This will give them an average finishing position, rearrange the list and move those below that finishing time down one place.  This will maintain their previous handicap for the next race – or very close to it.  Doing other changes is of course possible but be careful as the whole idea of this program is to remove any personal bias or suggestion of favouring particular sailors.

Goto Top

 

What about late comers?

You have started and recorded one/two races and a late comer arrives and wants to sail.  After berating the late comer for their tardiness and reminding them that they will be getting max points plus one for any missed race and they still want in then OK go back to the skipper list by pressing the ‘Yacht Selection Sheet’ area and add them.  Press the ‘Sort and Transfer Yachts to Timing Sheet’ button and they will appear as an unprocessed yacht number button.  You need to return to race 1 on the timing sheet and manually type the yacht number and a ‘DNS’ into the two columns then process the handicap again for race 1.  Repeat for race two etc up to the last race you have processed.  A bit cumbersome but it works.

Goto Top

 

What if someone withdraws before the end of the event

Their yacht number button will still be there so it needs processing.  When all other yachts have finished each race, manually give them a ‘DNS’ (Or DNC) result for each race.

Goto Top

 

Processing Results

This is fairly self explanatory as pressing the ‘Process Results’ button will do everything for you working out the finishing score for all yachts arranging them in finishing order including sorting those with the same points based on the normal rules about the number of each finishing position etc.   For viewing, the list can be reordered using the options listed.   Remember the program was initially designed for a handicap teams race so it will do this anyway based on the best 4 yachts in each team (club)  This is now based on one less than the minimum number from each team with a minimum of 4 yachts.  This can simply be ignored if your race is an individual yacht event.  Clicking the ‘Export’ and ‘Open Export’ options allows a view of a formatted spreadsheet of the results for display in your club’s newsletter or web site.  There is a sheet generated which displays the variations of the handicaps over the series of races and is useful to show what happens to handicaps over the series.  These values can be used as an initial handicap in subsequent series.

Goto Top

 

Action Buttons don’t seem to work

Most likely you have entered a value into the spreadsheet and forgotten to press the ‘Enter Key or Tab Key or move to another cell.  Only then will the action buttons become active!

Goto Top

 

Concept by Trevor Jeffree

Witten by Ben Morris