Manual for F5J Score.Xls

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 OpenOffice.Org (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 (<10-6) is added to each normalized score. This fairly prevents the RANK function from giving an error message in case of an exact tie.

In columns L and M, a sorted list of pilots by rank is included. If desired, the results for each round may be printed and posted after it is flown. Be sure to select only the area that you want before printing and just print this selection.

The Totals Page

Totals of the normalized scores are summarized on the Totals page for all completed rounds.

Class: / Outrunner
Pilots / Round1 / Round2 / Round3
Dan Tandberg / 973 / 991 / 991
Buzz Averill / 489 / 1000 / 1000
Richard Shagam / 1000 / 1000 / 1000
Bruce Twining / 601 / 788 / 788
Rocky Stone / 1000 / 541 / 541
Richard Dick / 632 / 656 / 656

The normalized scores are totaled in column AA.

A throw-out round is calculated each for completed set of six rounds. This may be adjusted by altering the ranges in the formulas in columns AB through AE (preferably by a good Excel programmer.) If no throw-out rounds are going to be used, just replace all of the formulas in columns AB through AE with numeric zeros. Final normalized scores, ranks, and Pilot names are provided in columns AF through AH.

A Sorted Totals page follows. Here the winning pilot is at the top and the loser is at the bottom. This sheet is provided for printing out and posting a complete summary of all scores at the end of the contest. It is written in a way that avoids having to use the Excel “Sort” command, which can scramble the scores and create big problems during a contest.

For printing out the final scores, totals, and ranks, you can just use the “Hide Columns” command in Excel. (Alternately, you can set the selected unneeded column widths to zero.) The worksheets will be damaged if you delete the unused columns. After the unused round columns (and unneeded throw-out columns) are hidden, they will not show up during printing.

Score Cards.

Small score cards are provided for each pilot. Four cards to a page are automatically generated. These are cut up with scissors to provide individual small cards. Eight rounds are to be recorded on each card. These are found on the last three tabbed pages labeled

Score Cards 1-8

Score Cards 9-16

Score Cards 17-24(The numbers indicate the rounds.)

The flight group for each round is to be written in by the timer rather than being put in automatically. This is to avoid the problem of last minute changes in flight group due to problems with a pilot’s model right before launch, etc. If a pilot flies in the wrong flight group for a round, this must be changed by hand in the Flight Matrix Generator to ensure proper scoring.

The pilot’s name is automatically entered from the list, as is the frequency and model name. For each flight, the timer should write in the minutes, seconds, landing points (or penalty), and initial the card. At the end of each round, these are turned in at the computer data entry table. They are handed back out to the timers at the beginning of each subsequent round.

If more than 48 pilot cards are needed, the last page of score cards (page 12) may be copied and pasted below as needed.

To print the needed cards, select just those pages containing pilot names, and then print. We print out an extra page of the blank cards at the bottom as well in case of latecomers. Use the “print preview” and “page break preview” command to make sure the print job will be tidy.

If you are using a color printer it is useful to have a different class label color for each class (the default is set to red background). This helps to keep score cards from different classes from getting mixed up. You can change the class label background color, foreground color, oreven the font by editing the conditional formatting rule. You will have to unprotect the worksheet to do this. Since these conditional formatting rules are specific for each page in the workbook, you will have to do this for each of the three score card sheets individually if you are flying more than eight rounds.

Scoring Re-flights.

In some contests, re-flights may be allowed for a variety of reasons. If a pilot is awarded a re-flight, there is the question of how to score this fairly. What we do is allow the pilot to fly an additional flight group in the subsequent round. This extra flight is scored by hand, and the normalized score is hand entered into the normalized score cell of the earlier round that is being re-flown. Use the formula

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

The worksheet will need to be temporarily unprotected to do all this. The minutes, seconds, and landing point may then be also entered (for later reference), if desired.

Possible Modifications:

In the sections above, a number of possible modifications have been suggested. These include increasing (or decreasing) the maximum number of pilots, changing the maximum number of rounds, and changing the number of rounds for each throw-out round allowed. As presented, this workbook has Worksheet Protection turned on. If you want to try modifications, you will need to first turn off protection before modifying anything.

I welcome hearing any suggestions, comments, criticisms, or errors that you come across.

3-30-10

Dan Tandberg, MD

Albuquerque Soaring Association

Albuquerque, NM

1