How To Maintain a Percentage Ladder with Worksheets
or
Fun with Spreadsheets

This is a brain dump of how I ran the weekly ladder leagues at Happy Trails during the 2003-2004 season. This was a 10 week percentage system where players played 3 games to 15 with a maximum of 45 points (first team to 15 wins, no two points required). A player's scores are maintained for the last 10 weeks of play and the percentage of the maximum points determines their ranking. I chose to interpret the last 10 weeks as last ten weeks of league play not the player's last ten weeks of play. It was easier in the spreadsheet.

The weekly process is not hard but you should be familiar with basic spreadsheet commands; specifically, 1) insert and delete rows and columns, 2) copying and pasting blocks of cells from one sheet to another, 3) sorting rows of information specifying the columns to sort by, and 4) formatting with borders and shadings. I'll go through things step-by-step, but your spreadsheet program may be slightly different. The sample sheets provided here are for Microsoft Works Version 7, so you should be able to pull them up Excel or other program with the correct filter or converter.

Let's get started:

1) Determine the league rules:

• How to place new players entering the league

• What to do for no-shows when scheduled players don't show up.

• When and how the schedule will be posted.

• Who's responsible for finding substitutes.

Every community may have different needs and philosophies.

2) Get your initial player list together and rank them 1 to n. This can sometimes be difficult because everyone has different opinions but in the end, someone has to make the decision. You will make mistakes but play will correct the misplacement and players after 5-6 weeks will probably seek their position, up or down. I tell people to prove my ranking wrong.

3) Create your initial standings worksheet by filing the players in order. Save this spreadsheet as "initial.wks". Each week you'll take the previous week's standings file and create a new for the new week. I suggest naming the weekly sheets using the date of play i.e. "Standings_yymmdd".

 The sample weekly standings sheet is here: Weekly_Standings (in MS Works format for download, click here Standings_Spreadsheet ). Here's is an explanation of each column:

• (column 1: Rank) - the player's rank 1-n. The first row value is 1, the second is "=row1+1" and then this formula is copied down thru the rest. This was a lazy way of numbering them without typing 1,2,3,4.. in each row.

• Name - the player's name

• Phone - the player's phone number

• J8-A08 - These columns are the player's total point each week. J8 is short for January 8th, J15 - January 15th, and so forth to A08 for April 8th. It was shorter then 01/08 or 04/08 so that the sheet would fit on a landscape 8 1/2 x 11. The column on the right is the most recent week of play.

• Tot - This is a total of the previous ten columns being the last ten columns of play. The first column is to know what points the player is losing when they play past 10 weeks ( SUM() of the columns)..

• Wks - the number of weeks the player has played in the last ten weeks (the number of columns with totals in them 1-10).

• POS - A computed value of the maximum points (Wks * 45) for each player.

• XX% - The player's current percentage (Tot/POS)

• B - A count of the number of byes the player has had.

• A - Playing status (Y=yes-playing, T=assigned a bye, N=no-not playing, G=gone).

• Notes - Any notes to help you remember things.

In the actual spreadsheet a table is maintained out to the right that shows the player's name, phone, old total, what's added, what's subtracted, and the new percentage. If you keep the formulas copied down when new players are added, this is a shorter posting for players instead of the big gory thing. It also explains why they went down when the won but had a bigger winning total fall off as the 11th week.

After your initial list is created, the rest below are the normal weekly workings.

Weekly before play

3.5) After adding new players to the sheet each week, be sure to copy down the formulas in the columns First column(Rank), Tot, and XX%. This can be done by selecting the cells in that column and doing a fill down to copy the formula from the top cell all the way down.

4) Add new player's to your standings. Place them according to you rules and philosophy for their initial week of play.  A number of clubs say to put them at the bottom with a percentage of 0. Then depending on their play, they will be placed according to their percentage.

5) Next sort the players information by their status (A) and their current percentage (XX%), both descending.

• select the cells of all the players from their names thru the notes (so that the row information is kept together).

• select Tools => Sort.

• select the status column (A) to be the first sort field with descending selected.

• select the percentage column (XX%) to be the second sort field with descending selected.

This will put the player's in order by status and percentage. All the active players will be list first by percentage, then the player's not playing, and lastly the player's taken off the ladder. (Note the codes Y, T, N, and G are used so they get sorted this way.)

Note: The first column is the rank position and we never include it in a sort.

6) Looking at your list, see if you have an even number of players to fill the courts. There is probably a 75% chance you won't. You'll have at the end of your actives 1-3 players that are playing but not a complete foursome. Working up from the bottom, look for players without a bye (the B column) and increase their count and set their status to T.

7) If you've had to assign byes in step 6, redo your sort in step 5. You should now have an even number of players to fill the courts.

7.5) Save this weekly standings roster (named something like "Standings_yymmdd").

8) Now pull up your master weekly roster. Click here to see it on the web. Click here to download the MS Works spreadsheet. This is the sheet you'll post for the players and they will also use it to record their scores and total up their points.

At the beginning, change this sheet to be the maximum number of courts that you'll need and at this point put in your court numbers and times. There are no formulas on this sheet.

With your standings spreadsheet pulled up, select the cells with the names and phone numbers for the people playing and copy them to the clipboard (usually Alt-E C). Now switch to the blank weekly play roster and select the cell for the name of the first play time and paste in the names and phone numbers. This should fill in the correct number of courts from what you determined earlier based on the number of players available.

Now delete the extra court times by highlighting the rows and selecting "Delete Rows".

9) For your players that have byes: the odd number of players. Go back to your weekly roster and select their names and phones and then paste them at the bottom of your weekly roster into the Byes/Subs. You should have no more than three of them. By our rules, these are the people to call first for subs.

10) You can then copy your inactive people to the bottom so they know that they are not forgotten. People like to see their name someplace and also you'll have everybody listed someplace.

11) Now save this file under a different name (suggest "Roster_yymmdd") so you'll have one for each week.

12) Now post your roster, play the games, and have the players record their scores and total them up.

Weekly After Play

13) After play, verify the totals on the play roster.

14) Pull up your weekly standings sheet you saved back in 7.5. Select the cells for the last ten weeks of scores for the players and the titles: in the sample it would be J15-A08 and paste them one column to the left into J8. This moves the scores over one week, for our new week's scores. Clear the scores in last column (original A08) and change the date at the top to the new date.

15) With your weekly roster totals, you now fill in the player's totals. They should be in the exact same order since we copied them from this sheet to the roster.

My roster sheet has every other court in yellow so it's easier to read. To create this effect with the thicker borders: Highlight the cells, select Format->Borders and select a thickness of the border and you want inside and outside borders. Then select Format->Shading and select Yellow Color, Yellow Pattern Color, and Solid Pattern. This will make them all yellow. Then select the cells you want white and select Format->Shading and make their pattern None.

16) After entering the scores, update the number of "Wks" played column to reflect the correct number of weeks played in the last ten weeks. If the player didn't play one week, either leave it blank or put an x. If the formulas have been maintained, the Tot should be the sum of the last ten weeks and your percentage (XX%) will be calculated correctly.

17) Sort you players and their information (without the rank column) in descending order by percentage and you'll have your standings and be ready for the next week and back at step 3.5

That's the basic steps.  Here are some recommendations:

1) Take your time.

2) Verify whose playing and not playing before starting and have their status set. This is the hardest to keep notes on people off and when they will restart play.

3) Don't do any of this til after your scheduling cut off time when players should notify you that they will be off. As a rule, if I did the schedule early, someone will call and you go through the whole process again to recreate the schedule to take them off and shuffle everyone up. Even after the specified time, luck would have it that someone would call when your ready to post the printed schedule. That's your call whether to go back and redo the schedule.

4) Remember, any mistake leads to starting the process over.

For further info or questions contact Bob Lanius.