SWCP



Manual for F5J_Score.xls

A Generalized Excel Workbook for Flight Group Assignment, Score Entry, and Score Card Printing for F5J-Style Motorized Sailplane Contests

Dan Tandberg, MD

Albuquerque Soaring Association

Albuquerque, New Mexico

Introduction

This Excel workbook was created for our own “Albuquerque Rules” F5J electric sailplane contests, but can easily be adapted for other contest formats. As provided, it will accept up to 48 pilots and scores for up to 24 rounds. Normalized scores within flight groups and pilot ranks are calculated for each round and for the overall contest. Small scoring sheets for each pilot can be printed out for recording scores by the timer. The program has been written in such a way that data can be safely entered by minimally trained “volunteers”, with oversight by a more experienced scorekeeper.

Multiple flight groups for each round are provided for, with up to 10 flight groups being allowed. This is because we fly “man-on-man” and must all time and call air for each other. Thus we almost always need at least two or perhaps three flight groups per round. This workbook makes pilot assignment to flight groups using a sophisticated algorithm that provides truly random assignments with equal (or nearly equal) numbers of pilots flying in each group. After all the pilot names are entered, the flight group generator matrix needs to be copied and “paste specialed” to convert the formulas to values and stop recalculation of flight groups during subsequent data entry.

As more pilots move to the 2.4 GHz band, frequency conflicts on 72 MHz have become less common. We currently deal with the problem of possible conflicts on 72 MHz by asking each pilot to submit an alternative frequency when signing up for the contest. The current version of this program does not automatically screen for such conflicts.

The formulas in the workbook are written in a very general way to allow for easy expansion or reduction in contest size by a moderately-experienced Excel programmer. The use of macros and visual basic code has been avoided, so the workbook will run smoothly without major alteration under non-Microsoft spreadsheet applications. The Calc application in (which is free) will also run this program perfectly.

The original workbook file should be backed up so that a clean fresh copy is always available. A separate copy of the workbook is used for each sailplane class being flown. The workbook should be saved periodically during data entry for security. We save to the hard drive and also to a memory stick after each round for extra safety. Intermediate saves should be made with the “Save As” command and the filename should be changed a bit each time, for example

Outrunner_09-2009_01.xls, Outrunner_09-2009_02.xls, Outrunner_09-2009_03.xls, and so on.

This way, if someone trips on the power cord, only one round will have to be re-entered.

If the contest is to have a separately scored fly-off at the end, a new workbook should be started for each class, and only the “fly-off” pilots names need be copied and entered into the new fly-off pilot list.

Throw-out round scoring has been provided for. We use one throw-out round for each block of six rounds completed, but this feature can easily be modified or not used at all.

Users should thoroughly familiarize themselves with this workbook before using it in a big contest. It is a good idea to practice by actually entering an old set of scores. Preferably, at least two people at the field should be able to troubleshoot and fix the problem if something gets “broken” during a contest. We always try to have a backup computer, battery, inverter, memory stick, and spare printer cartridge available at the field. Laptop computer screens need to be shaded for easy viewing; we use a canopy-type tent, tarps, and a cardboard box.

Details of the Input, Pilots List, Flight Matrix Generator, Data Entry, Totals, and Score Cards workbook pages are discussed below.

The Input Sheet

This sheet allows several things to be changed and provides basic instructions. This spreadsheet is set up for a maximum of 48 pilots and up to 24 rounds, but can easily be modified.

The first dropdown box (cell A3) allows you to choose the name of the class that will be recorded. This will be written throughout the other sheets. You can modify or add additional names to the classes allowed to suit your own needs in cells A46 to A56. Up to 10 class names are allowed.

The second dropdown box (cell A6) allows you to choose the number of flight groups. From one up to 10 flight groups are allowed. If you are using this workbook to score a contest without flight groups (say, a TD contest) just set this parameter to one.

A throw-out round is calculated for each completed set of six rounds. This may be adjusted by modifying the ranges in the formulas in columns AB through AE in the Totals sheet. If no throw-out rounds are going to be used, you can just replace the formulas in columns AB through AE (cells AB3 to AE50) in the Totals page with zeros.

Pilot and Frequency Entry Page.

Pilots, their frequencies, and the names of their models are entered in columns B, C and D. Cells below the last pilot should be left blank.

|Class= |Outrunner | | |

|Index |Pilot |Freq |Model |

|1 |Dan Tandberg |35 |Cookie 85 |

|2 |Buzz Averill |33 |Sky Sergio |

|3 |Richard Shagam |23 |Wind Dancer |

|4 |Bruce Twining |2.4 |Electron |

|5 |Rocky Stone |2.4 |Electron |

|6 |Richard Dick |45 |Gentle Lady |

|7 | | | |

Up to 48 Pilots may be entered for each class.

The Flight Group Generator Matrix

This sheet is the heart of the program. It randomly assigns each pilot to one of several flight groups (set up on the Input page) for each round, while insuring nearly equal group sizes.

After all the pilot names are entered, you should press the F9 function key a few more times (without looking at the computer screen) to recalculate the Generator sheet. This will insure that the flight group assignments are truly fair, random, and “blinded.” The flight group generator matrix that contains pilot's names (say from A1 to BT12 if there are 10 pilots) then needs to be selected and Copied. The “Paste Special” command with the “Values” button selected is then used. This converts all the formulas in the rows containing pilots to their actual values and stops all further calculation of the flight groups during subsequent data entry. It also leaves the formulas in the unused rows in case a pilot or two must be entered later. Failure to carry out this step will lead to chaos!

How it works: (for technical readers only)

Hidden in the gray vertical bars are Excel formulas to generate random decimal numbers between 0 and 1 for each pilot and for each round. For example, in cell C3, the formula =IF($A3="","",RAND()) puts a random number in this cell.

In cell B3, the complicated formula

=VLOOKUP(MOD(RANK(C3,OFFSET(C$3,0,0,COUNTA($A:$A),1)),Inputs!$A$6),Inputs!$A$33:$B$42,2)

randomly assigns a flight group to each pilot and keeps the group sizes as near to equal as possible. The number of flight groups is chosen on the Inputs page and can be from one to 10.

The OFFSET and COUNTA functions create a “variable range reference” that ignores the rows below the last pilot in the list.

The RANK function sorts the pilots according to their random numbers in column C.

The MOD function divides up the pilots into n (nearly equal) flight groups.

The VLOOKUP formula reads the group name from the table at the bottom of the Inputs page.

The dollar signs are used in the formula create absolute or mixed cell references. They make the formulas so they can be copied correctly to lower rows and rightward columns in case the matrix needs to be made larger (more pilots or more rounds).

Data Entry Pages.

Data entry for each round is only allowed in columns D, E, and F. Each round has its own tabbed sheet labeled ‘Round 1’, ‘Round 2’, through ‘Round 24’. Flight minutes and seconds are entered in columns D and E. Landing points (or negative penalty) are entered in column F. Data validation routines are used to keep text or non-integer values from being accidentally entered.

In column G, the total seconds are calculated and the landing points are added. If there is a landing penalty, say -30, for landing after the end of the window, this is included in the calculation. Columns S through AB contain a work area for the maximum score calculations for each flight group. These are then used to calculate the normalized scores for each flight group in column I. Normalized scores are calculated as simple proportions of the best pilot’s score, multiplied by 1000:

Normalized scores = 1000*(pilot’s score for the round/winning score for the flight group)

A tiny random decimal ( ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download