PDA

View Full Version : Tipping Spreadsheets (for tournament managers)


Frederik
Jan 29th, 2012, 12:01 AM
A lot of people asked for a tipping spreadsheet...

Here are the spreadsheets that I created:

DOWNLOAD:

32-player draw (5 rounds): http://www.mediafire.com/download/dkwc9jx4gbblsbr/Tipping_32.xlsx
64-player draw (6 rounds): http://www.mediafire.com/download/m0d5cgzhd4mrui5/Tipping_64.xlsx
128-player draw (7 rounds): http://www.mediafire.com/download/5mlmfigfzzyezg1/Tipping_128.xlsx

What it does:

- calculates scores
- creates a ranking
- breaks ties

The spreadsheets only work for up to 62 tipping players. If you need a "bigger" sheet... send me a PM.


Please read this tutorial on how to use the spreadsheet before using it:


Tutorial:

Step 1: Open the Excel file:

http://img515.imageshack.us/img515/5305/25985431.jpg

Step 2: Type in the entry-list

http://img819.imageshack.us/img819/5171/11964583.th.jpg (http://img819.imageshack.us/img819/5171/11964583.jpg)

Step 3: Highlight the names and select "Sort&Filter"->"Sort A to Z" to sort the names from A to Z

http://img853.imageshack.us/img853/5379/97753027.th.jpg (http://img853.imageshack.us/img853/5379/97753027.jpg)

Step 4: select "continue with the current selection"

http://img31.imageshack.us/img31/2595/48599962.jpg

Step 5: Put in everyone's picks - TIP: you only have to put in "A", in this case for "Azarenka" if you already put in "Azarenka" in the same row. If both players' names start with the same letter (for example: Schiavone vs. Sharapova) use F. Schiavone and M. Sharapova (you don't have to but it'll save you a lot of time)

The OOP of the day was: (example)

Azarenka vs. Watson
Dellacqua vs. Jovanovski
Jankovic vs. Robson
Brianti vs. Falconi
Voskoboeva vs. Wickmayer

http://img96.imageshack.us/img96/1691/82617362.th.jpg (http://img96.imageshack.us/img96/1691/82617362.jpg)

Step 6: AFTER THE MATCHES ARE FINISHED: select "find&select"->"replace"

http://img705.imageshack.us/img705/8339/63731902.th.jpg (http://img705.imageshack.us/img705/8339/63731902.jpg)

Step 7: Replace the winner with the correct amount of points (1 for Round 1; 2 for Round 2 etc.). Type in the name of the winner in "Find what" (in this case "Azarenka") and put in the points in "replace with" (1 for Round 1; 2 for Round 2 etc.).

http://img810.imageshack.us/img810/5603/27801595.th.jpg (http://img810.imageshack.us/img810/5603/27801595.jpg)

http://img819.imageshack.us/img819/438/27437841.th.jpg (http://img819.imageshack.us/img819/438/27437841.jpg)

Azarenka, Dellacqua, Jankovic, Brianti and Voskoboeva won their first round matches (1 point).

http://img38.imageshack.us/img38/3509/21287954.th.jpg (http://img38.imageshack.us/img38/3509/21287954.jpg)

Step 8: Replace the loser's names with a "0" to avoid problems in later rounds.

http://img7.imageshack.us/img7/5981/44640140.th.jpg (http://img7.imageshack.us/img7/5981/44640140.jpg)

Step 9: Hide completed rounds (not necessary but recommended)

http://img33.imageshack.us/img33/452/79490271.th.jpg (http://img33.imageshack.us/img33/452/79490271.jpg)

http://img850.imageshack.us/img850/2127/21348446.th.jpg (http://img850.imageshack.us/img850/2127/21348446.jpg)

Step 10: Switch to the "Ranking" sheet to have a look at the (automatically) calculated ranking.

http://img813.imageshack.us/img813/8406/27925100.th.jpg (http://img813.imageshack.us/img813/8406/27925100.jpg)

Step 11: Repeat steps 5-10 for the following rounds (minus the final) - (remember to change the amount of points according to the round)

http://img69.imageshack.us/img69/1960/95221288.th.jpg (http://img69.imageshack.us/img69/1960/95221288.jpg)

http://img827.imageshack.us/img827/2254/91261806.th.jpg (http://img827.imageshack.us/img827/2254/91261806.jpg)

Step 12: BEFORE THE FINAL: put in everyone's picks (just like in the other rounds) AND the number of games in the row "Games" and the time of the pick in "Time". Time means the position at which a player posted his pick:

example:

These players posted a pick for the final (in the following order):

Djezonfly
JustPetko
Drake1980
klever58
SamR03A
Kev.

etc.

That means that you have to put in a "1" for Djezonfly, a "2" for JustPetko, a "3" for Drake1980 etc under "time".


http://img268.imageshack.us/img268/9273/66404108.th.jpg (http://img268.imageshack.us/img268/9273/66404108.jpg)

Step 13: BEFORE THE FINAL: Go to the "Ranking" sheet and replace "Player A" and "Player B" (found in the yellow (they aren't yellow in the actual spreadsheet) cells) with the names of the two finalists to get a projected ranking which includes the 2 possible outcomes. (OPTIONAL)

http://img141.imageshack.us/img141/2789/25711132.th.jpg (http://img141.imageshack.us/img141/2789/25711132.jpg)

Step 14: AFTER THE FINAL: Put in the number of games (example: Azarenka def. Sharapova 6-3 6-0 ---> 15)

http://img259.imageshack.us/img259/891/55878770.th.jpg (http://img259.imageshack.us/img259/891/55878770.jpg)

Step 15: Replace the winner's name (in the "Picks" sheet) with the correct number of points (in this case (Grand Slam): 7 points)

http://img546.imageshack.us/img546/4594/22307808.th.jpg (http://img546.imageshack.us/img546/4594/22307808.jpg)

Step 16: Switch to the "Ranking" sheet to see the final score

http://img718.imageshack.us/img718/3395/91263704.th.jpg (http://img718.imageshack.us/img718/3395/91263704.jpg)

Step 17: Highlight the ranking and select "copy"->"copy as picture"->"bitmap" if you want to post the score in the forum. (You also have to paste it into a program like Photoshop and upload it to an imagehoster like "ImageShack oder ImageBanana" if you want to post it here)

http://img407.imageshack.us/img407/5467/90403212.th.jpg (http://img407.imageshack.us/img407/5467/90403212.jpg)

http://img171.imageshack.us/img171/1871/95005425.th.jpg (http://img171.imageshack.us/img171/1871/95005425.jpg)


IMPORTANT:

Here are a couple of names that you shouldn't type in/replace:

King: Use V. King or Vania King or whatever you want but NEVER use the replace-option for "king". You won't be able to use the spreadsheet anymore after replacing "king"... so be careful.
Radwanska, Rodionova etc.: use A. Radwanska and U. Radwanska if they're both playing the tournament
Kirilenko: don't use Kirilenko if DAVAJ MKirilenko is playing the tournament
Li: the name "li" is too short. Use Na Li if you don't want the spreadsheet to replace "Lisicki" with "1sicki" (if it was a 1st round match)

Frederik
Apr 14th, 2012, 04:31 PM
let me know if the files are down (like they are now)... I'll re-upload them asap

WhoAmI?
Apr 20th, 2012, 08:04 PM
You can use King if you choose Find&Search -> Replace, there's a button Options>>
http://img844.imageshack.us/img844/4852/23796517.png

mark Match entire cell contents
http://img401.imageshack.us/img401/1059/76655744.png

This shouldn't screw up the formulas.

Frederik
May 4th, 2012, 08:33 PM
thanks ;)

Frederik
Aug 17th, 2012, 08:52 PM
I'm working on a new/better spreadsheet.

I'll either post it tomorrow or in 3 weeks (after my vacation)

:wavey:

Håkon
Sep 17th, 2012, 07:54 AM
How's the new spreadsheet coming along? :angel:

Frederik
Sep 17th, 2012, 12:18 PM
I posted it in some of the tipping threads.

I'll post it here later.

Frederik
Sep 22nd, 2012, 03:44 AM
http://www.tennisforum.com/showpost.php?p=22047944&postcount=19

It's here ;)

Edit:

looks like the file has been deleted... I'll re-upload it soon :p

Frederik
Jun 25th, 2013, 10:42 PM
links updated

mariusz_pol
Aug 5th, 2013, 06:51 AM
Does this spreadsheet also count bonus points (every correct point one point and points for TOP8)?

Frederik
Aug 5th, 2013, 10:21 AM
no

the rule didn't exist when I created this spreadsheet.