How to use results spreadsheet

Following the draw, enter the names of each competing club in the column below the lane number in which they will compete.

Ensure that “number of competing clubs =” is set to the actual number competing in this gala (by default 8, but reduce where necessary). This ensures that maximum points for a win = number of clubs competing.

As the results come in, enter the times for each lane into the grey boxes:
min   sec     hh

The formulae in the spreadsheet calculate the place based on all times entered, and then calculate the points based on the place and the number of clubs competing

Therefore, you must not enter a time if the club should not earn points for any reason: DQ, DNS, DNF, over-age, under-age (e.g. under 9 years). There is a column DQ Code in which you may write any comments you like to explain why a team has been given no time

Equally, if no time was recorded (e.g. stop watch failed and chief timekeeper also did not record a time), a time must be entered, in order to create a place and points. The correct way is for the Referee to issue a Referee’s Decision – award a time 1/100th sec. faster than the next placed swimmer.

Avoid editing the place column.  Unless every place column for that event is edited, the other place columns will still try and calculate place based on the other times which have been entered. You could therefore end up with two teams with the same place or wrong places resulting in wrong points.

If a formulae is not able to calculate you will see #VALUE! but mistakes are not always that obvious.

The Checksum column is there to guide you to finding input errors. e.g. if there are 8 teams 8+7+6+5+4+3+2+1 = 36

If the number is not 36 you should know why.  e.g. 1 team DQd you expect the number to be 35, two DQd 33 etc.

The only reason you should need to unlock the spreadsheet and edit any of the columns is in the event two teams have a dead tie on time. You would see that the checksum number is 37 because both have been awarded the higher point, whereas they should have that point split between them so equal first would each gain 7.5 points and checksum is 36.

In this case, edit the points column only. The place column will already show them both as 1st and therefore the next placed team will show as 3rd and points for those teams will be correct.

 

Sheet2

Sort the teams and their gala points in decending order. Award to winning team the max number of league points e.g. 8 (7 if only 7 teams compete) and each other club their league points in decending order down to 1.  If there is a tie on gala points split the league point to a half.

If there are less than 8 teams competing in your gala AND a greater number of teams competing in the other gala in your division, then both your gala and league points shall be adjusted thus:

Multiply by the fraction:                     number of clubs in the other gala

number of clubs in your gala

 

e.g.                                                            8/7     (this fraction is always >1)

and then round to the nearest half point

e.g. adjusted league points for a 7 team gala (assuming 8 in the other gala) will always  be

first place 8
second place 7
third place 6
fourth place 4.5
fifth place 3.5
sixth place 2
seventh place 1

 

In this manner, you can run a division with an odd number of competing teams without unfairly impacting who wins or who is relegated