Jump to content

TSB tournament auto-calculating spreadsheet


Recommended Posts

I put together a spreadsheet to be used at this weekend's ShoreStyle tournament in Jersey.  We all get annoyed when there is a major delay at the end of the group stage as the seedings are calculated and brackets figured out, and the idea here is to have that all done automatically.  You input the game scores, and the sheet automatically calculates not just w-l record and points differentials, but it also has a built-in tiebreaker function to rank players within groups and seed them overall (players are ranked, as usual, by winning percentage, points differential (with single-game differentials capped at 28 points to discourage score runups, and points scored). 


 


This was designed with your typical World-Cup style event in mind, but there's no reason you couldn't use it for any other TSB event where tracking records and seeding players is important.  It's good to track up to 100 players and 600 games, though it could easily be modified for more.


 


How to use:


 


Before the event begins:


 


1. Populate the "GroupList" tab first with the names of the groups you'll be using


2. Populate the "Coachlist" tab with the names of the competitors.  Pair the competitors with the groups in which they will be competing (the boxes in the "Group" column will have a drop-down list consisting of the Group names you input in the first step.  There is also space here for Tecmo nicknames and hometowns.


 


During the event:


- Inputting results is simple, and is done in the "Games" tab.  The boxes for "Home Coach" and "Away Coach," again, will have drop-down lists autopopulated by the coach names you input in the "Coachlist" tab. To ensure accurate tracking of results, misspellings of names or attempts to input names that aren't on the "Coachlist" will be rejected.  Same for team names: these need to be put in exactly as they appear on the TSB Team Select screen (i.e. "BUF.", "G.B.", "RAMS") - there is a dropdown for this, too.  There are also columns to track which player called the matchup.


- The "Standings" tab automatically tracks standings, including winning percentage, overall points differential (adjusted to cap margins of victory at 28), and Group and Overall seedings.  The one issue here, however, is that since the workbook is designed for up to 100 players and few events attract that many, there will be a number of blank rows - sometimes, these rows will be pushed to the top upon sorting.  To avoid this annoyance, once all the competitors have been input into the "Coachlist" tab, simply delete all the rows beneath the last competitor in the "Standings" tab.


-Just for fun, I've also included a "matchups" tab that calculates how many times each team gets used and how many instances of a particular matchup occur.  I've always wondered what the most common matchup is.


 


 


All the calculations are done in hidden tabs so that users can't disrupt the formulas.  This way, competitors can input their own results and don't need to be supervised.


 


Also, the sheet is prettiest when the "GF Tecmo Set 1" font is installed.  If you don't have it, you can download it here.


 


Hope this is helpful!


 


TSBTourneyScoresheet.xlsx

Edited by deadfaulkner
Link to comment
Share on other sites

  • 4 months later...

Very nice work!


 


One comment. The red on blue, blue on red color scheme is a bit hard on the eyes for reading Especially the red text on dark blue saturated background. 


 


Any way to get strength of schedule in the sheet? Then you could have a ranking based adjusted point margin based on schedule something similar to he SRS rankings on pro-football-reference if you are familiar with that.


 


Doing it in a spreadsheet can be tricky but you'd probably just need a vlookup to list of every competitor and then that could be added to the games sheet. Obviously the strength of schedule adjusts dynamically as more games get played. . Schedule adjusted point differential gives great power rankings but their might not be enough convergence in a tourney setting at least for just one tourney. Over multiple tourney's it could be useful for seeding

Link to comment
Share on other sites

This is pretty awesome. I'll have to play around with it a bit. I'm a programmer and tend to prefer actual databases to normal spreadsheets but this is pretty awesome. **applauds** 

 

I would love a database that achieves the same effect; in fact, we would LOVE something at northeasttecmo.com that would enable us to do live updates of our results and that would automatically update our player records.  As you can probably tell from the fact that I'm still using spreadsheets, it's never really stopped being 1997 for me.  But if you have any thoughts on how that might be accomplished, I'd love the insight.

 

 

One comment. The red on blue, blue on red color scheme is a bit hard on the eyes for reading Especially the red text on dark blue saturated background. 

 

Any way to get strength of schedule in the sheet? Then you could have a ranking based adjusted point margin based on schedule something similar to he SRS rankings on pro-football-reference if you are familiar with that.

 

As I mentioned above, it looks pretty good with the GF Tecmo Set 1 font; without it, it's harder to see for sure, but if this is something you find useful go ahead and change the background and text colors to whatever you like.

 

I could fit strength of schedule in there, sure.  I didn't do that here simply because the main purpose for this was to calculate TSB tourney standings and seedings, and in most tournaments there isn't enough schedule variation to make such a calculation meaningful.  But I have another spreadsheet for another purpose that uses the old TCS power rankings formula, which is basically what you're describing.  I'll play around with it this week and post a v2 with a SoS calculation in it.

Link to comment
Share on other sites

My suggestion would just to add a webapp that has a conenction to a database. If you have very little bit of the know-how it is very easy to do. I was going to do something exactly like that for another game I was a main broadcaster of but never really ended up doing it as I basically got shafted by the main dev. 


 


We will see what I end up with whenever I finally put up a website for my streaming. Its been a plan for a long time that i just never really moved forward with cause I didn't have the time. 


 


On another note... As far as the bright colors go... just go with a little more washed out version of the colors as it still keeps the same feel but makes it easier to read with any font. on that note i'm going to download the font now =)


Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...