[gblake]

Originally Posted by Brandon Zalinsky

Hi y'all,

I've been trying to create a program in Excel to

1) Generate a FRC-style 2v2 schedule from a pool of teams
2) take in data points from each match (each alliance RP+total points)
3) Rank teams based on RP

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 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:
(#_matches*100000000)+(RP*1000000)+(QP*10000)+(tot al_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