Hi y’all,
I’ve been trying to create a program in Excel to
Now, I’ve seen some resources like this and this, and I’ve made some progress, but I’m running into issues like a very poorly randomized schedule. Are there any resources available for this, or does anyone have insight into how to do this easily?
Thanks!
I had a problem once with Excel’s randomizing. Seemed that whenever the sheet opened up, I got the same random number to start with. So… I put in a couple of extra “gimme a random number” lines and it cleared up.
https://www.chiefdelphi.com/media/papers/3042. I think I titled the relevant subroutines with “random” in the title.
The better you can bound the problem, the better you can decide which problems aren’t really problems and which ones you need to address. As a first break point – how many teams do you anticipate, and how many matches per team in your tournament? In particular, how does the number of teams in the tournament compare to the number of matches each team should play in? The pivot point is each team playing one fewer match than there are teams in the tournament. In the case that they are equal, each team should be able to play on alliance once with each team, and against each team twice. Whether the field is larger or smaller will determine which problems your process will have to deal with. If that point is in the middle of your possibilities, you may wind up making two different programs. In any case, I’m not sure that excel is the right tool for the software (though it would be great for verifying the list as generated as meeting the criteria, and for doing the tracking after the matchups are done).
I’ve done this before and have just ended up using VEX’s Tournament manager. Works very well for generating 2v2 matches. Doesn’t help your exact situation but if you are willing to just copy out the schedule you could make this work.
I’m personally exploring things like the Swiss-system tournament for my own project. I haven’t implemented it so I cannot give any details, though.
I see two topics here: A) Generating match schedules, and B) Producing and updating rankings on-the-fly
A) Is pretty easy, if you are wise enough to produce your schedules ahead of your competition. If I can find my old results, I can send you some for possible numbers of teams ranging from small to pretty large. If I can’t find those past results, I can probably be convinced to find the old software and regenerate them.
Neither you, nor VEX, nor FIRST, ever need to create a schedule at an event. Instead, at an event, you only need to randomly assign (and reassign, as necessary) the real teams at the event to the placeholder team slots in an appropriate pre-computed schedule.
If you have 24 teams at your event, you grab the precomputed 24-team schedule. Pick a pseudo-random point within the schedule to start extracting match assignments, and randomly make one-for-one substitutions of each of your actual teams for one of the imaginary teams in the schedule.
So, with a fistful of pre-computed schedules, and the ability to cut-and-paste the match data into a spreadsheet, this part of the problem is put to bed.
B) I have an ugly spreadsheet that (along with the ugly parts) contains a simple way to produce (in the spreadsheet) rankings. To update the rankings (after new match results have been entered into the spreadsheet), you tell Excel to recompute all formulae (automatically, or manually - See File->Options->Formulas).
However, don’t allow reevaluating all your formulae to change the random assignment of real teams to the fake teams in the original schedule. Use a manually entered seed in the assignment process, if that assignment process is part of your spreadsheet.
Basically, you blend all of a team’s ranking info into a big integer, and then use Excel’s Large function to find the 1st, 2nd, 3rd, or whatever ranked team by examining the integers of all your teams.
To create the big integer you do something like this:
(#_matches100000000)+(RP1000000)+(QP*10000)+(total_points_scored)
You will need to pay attention to things like not letting total_points_scored grow large enough collide with the QP*10000 term.
I can send you my ugly spreadsheet that includes a version of both A & B, along with some other things (like a referee’s match-score-entry sheet for the 2008 FVC game). I can correspond or chat with you as we both try to figure out what I was thinking 8 years ago.
On the other hand, if you take care of A & B, the rest is tedious, but not too hard to do from scratch on your own.
Blake
PS: I found where I uploaded fortyfive, 20-match, 2-vs-2, schedules (for any number of teams, between 5 and 51, attending an event) into the VEX Forum roughly 8 years ago. VEX Forum Thread If you can’t download the file attached to my post in that forum, let me know and I’ll get it to you some other way.
PPS: For more info, see here Other CD thread, and here Another other CD thread
Do you really want it similar to FRC algorithm? For 2v2, I believe you can do a geometric progression to ensure even partners an opponents. Such a progression does not work well for 3v3 though.
Great questions. I’ve been trying to look at a maximum of 36 teams, and I’ve been to generating the schedules in “sets” of 9 matches (with fair surrogate-ing based on numbers of teams not divisible by 4). In order to make each team play 5 matches, I just generated 5 sets of 9 matches. The randomness issue came up with a repetition of matches between those match sets.
Looks wicked neat. I’ll definitely check that out.
I think I follow you. By how ugly do you mean ugly? Is it spaghetti or just a lot of information? I’ll check out that vexforum post, I didn’t even think of going to VEX for this, they’ve been doing 2v2 for years.
It’s not spaghetti, but it is somewhere in the neighborhood of Mac and Cheese.
If I were going to use a spreadsheet(s) to run a scrimmage, I think I would cherry pick methods/algorithms from my old spreadsheet tabs that were cobbled together while I was leaning what I wanted, and I would put those capabilities into a new workbook while using hindsight to give the new workbook a good architecture.
You are welcome to a copy of the old file, and some advice from me about it.
Blake