I use spreadsheets to record my field day log.  I don’t need to use a computer in the field – I work with computers all day and the last thing I want on a field day is yet another day of working a computer.  A paper log is quite good enough, and avoids odd things happening due to incorrect program logic, or wrong callsign recording.

In the John Moyle Memorial field day rules you need to record the grid locators of stations worked on vhf/uhf. Then you need a column for distance, then you need to work out the points earned by that contact.

I use Tiny Locator, a nice little application from ON6MU, to calculate distances from grid squares.  There are spreadsheet add-ins available for Excel, but I decided to just use an external tool and copy/paste its results into my spreadsheet. By doing the calculations only once for each callsign, the process is cut down to the minimum effort required.

I copy all the callsigns worked, with their locator codes as recorded during the contest, into a new worksheet called Grids.  Then I sort by callsign and remove the duplicates.  Then I do all the distance calculations with the help of Tiny Logger and record the distance for each station worked.

Back in the log page itself I then insert formulas into the Grid locator, distance and points earned cells of each row.

The grid locator can either be the original locator recorded during the contest, or the data copied from the Grids worksheet for that callsign.  Similarly for the distance and points data.

The formula to copy the grid locator for any callsign in the Grids sheet is =VLOOKUP($E2;$GRIDS.$A$1:$C$28;2) for OpenOffice sheet users, or =VLOOKUP($E2,$GRIDS!$A$1:$C$28,2) for Excel.

Similarly you can get the distance using the next column of the Grids sheet.

The points are calculated using a similar technique.  The score table is set up in another sheet, in the following format.

The formula to look up the points for a distance is: =VLOOKUP(K2;$Scoretable.$A$1:$B$6;2) for Openoffice.org Spreadsheets.
Here $K2 is the cell containing the distance for the contact made. Scoretable is the sheet containing the table above in cells A1 to B6. Column 2 in the scoretable is the column containing the points to be scored. VLOOKUP returns the row containing the distance that is equal to the specified search value.  If that exact value cannot be found in column 1 of the table, it matches against the first row found that is greater than the value sought.  Thus a distance of 80 is greater than 49.9 but less than 99.9, so the row chosen is the one with a distance of 99.9, allocating 5 points to the contact.

You could continue to refine the formulas and the fine details, such as dealing with decimal points of distance correctly.  eg. what is the score for a distance of 99.999 km?  The simple table and formulas used would give the wrong result and strictly speaking the scoring table specified in the rules does not state the score for distances between 49 and 50 km, etc.  However as a way of saving typing and avoiding keying errors in distances, this approach works well enough.  Rounding the distances appropriately is also something to consider.

Summary

Using a combination of plain spreadsheets and formulas, you can ensure the grid locator, distance and point score for each contact is consistently recorded in your log sheet.