White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams [Archive]

Joe Ross

17-03-2005, 04:06

Hi Joe, I found a couple of problems.

Besides entering the teams in the "TEAM NAME DATA" worksheet, you must also enter the team number in the "Team Summary" worksheet. That's relatively easy to fix by changing A2 on the "Team Summary" worksheet to ='TEAM NAME DATA'!A2 and dragging down.

You seem to be making the same mistake calculating RP as I initially did. The winner should get the lower of the unpenalized score of the winner or loser (per team update 15). Lets say Blue scores 19 and Red 25, and each has a 10 point penalty. The net score is Blue 9, Red 15. The losers RP is the losers net score, 9, which you get correct. However the winners RP is the lower of the teams raw score, which is 19. You currently have it at 15.

To fix this, change Z3 to =IF(P3="X",IF(S3=T3,T3,IF(S3=R3,MIN(X3,Q3),R3)),"-") instead of =IF(P3="X",IF(S3=T3,T3,IF(S3=R3,MIN(X3,R3),R3)),"-") and drag down

Personally, I like the way you had it better as a ranking system (the lower of the winners penalized score or the losers unpenalized score) but that's not the way it is stated in update 15.

With those changes, your spreadsheet produces the same results as my spreadsheet and SOAPs database

Joe Ross

17-03-2005, 06:00

One more:

It doesn't look like your spreadsheet calculates the highest match score to use as the tie breaker if the RP are equal.

Joe Johnson

17-03-2005, 06:57

Besides entering the teams in the "TEAM NAME DATA" worksheet, you must also enter the team number in the "Team Summary" worksheet. That's relatively easy to fix by changing A2 on the "Team Summary" worksheet to ='TEAM NAME DATA'!A2 and dragging down.

You are right, I just got sloppy at the end and missed this. This was page was the relative easy part and I was tired...

You seem to be making the same mistake calculating RP as I initially did. The winner should get the lower of the unpenalized score of the winner or loser (per team update 15). Lets say Blue scores 19 and Red 25, and each has a 10 point penalty. The net score is Blue 9, Red 15. The losers RP is the losers net score, 9, which you get correct. However the winners RP is the lower of the teams raw score, which is 19. You currently have it at 15.

I have to admit that I have NOT read the rules on this one. BUT, folks I know and trust have told me this is what FIRST intended by the rules whatever they SAY and that this is how they want the ranking implemented.

I will see what I can find out...

Joe J.

Joe Johnson

17-03-2005, 06:59

One more:

It doesn't look like your spreadsheet calculates the highest match score to use as the tie breaker if the RP are equal.
This is also because I didn't read the rules! Frankly, I just added the random tiebreaker column because the RANK function behaves better without ties.

I will have to think about how to implement this in my setup...

Joe J.

David Kelly

17-03-2005, 09:27

We're implementing this system as a backup at Boilermaker.
Anything else we need to know, besides the two tweaks previously mentioned?

Joe Johnson

17-03-2005, 09:44

Use the New one HERE (http://www.chiefdelphi.com/forums/papers.php?s=&action=single&paperid=469).

Fixes problems, implements highest QP points as the 3rd tiebreaker
1 Record
2 Average QP
3 Highest QP
4 Flip of a coin

Did NOT implement Joe Ross' suggestion of change to how QP's are given out when an opponent's penalties cause a loss (i.e. the losers would have won if they went penalty free). The winner's QP points are limited by their OWN QP points.

I have not read the rules, but I understand that this is how FIRST wants the points calculated.

Joe J.

Joe J.

Joe Ross

17-03-2005, 11:39

Fixes problems, implements highest QP points as the 3rd tiebreaker
1 Record
2 Average QP
3 Highest QP
4 Flip of a coin

In talking with Kenny Ardizzone, we both believe the rules call out the highest match score (the highest score that team had after penalties) rather then the highest QP.

Can you check that with your contacts as well?

Edit: also, the 30pt penalty field was removed, was that intentional?
Edit2: I can't read, it was intentional.

Kevin Sevcik

17-03-2005, 11:43

Just sent Joe a PM/Page. The current spreadsheet has a broken implementation of the Highest QP Calc. Right now it's basically just finding the highest QP of a team's first two matches.

Joe Johnson

17-03-2005, 13:04

Just sent Joe a PM/Page. The current spreadsheet has a broken implementation of the Highest QP Calc. Right now it's basically just finding the highest QP of a team's first two matches.
Thanks Kevin. Fixed it HERE (http://www.chiefdelphi.com/forums/papers.php?s=&action=single&paperid=470).

Joe J.

Karthik

17-03-2005, 13:21

In talking with Kenny Ardizzone, we both believe the rules call out the highest match score (the highest score that team had after penalties) rather then the highest QP.
From The Manual, bolding added for emphasis.

The Scoring System will use the following Ranking Method:

• Teams will be broken into Tiers based on their Qualifying Score. A Tier is made up of all teams with the same Qualifying Score.

• Within each Tier, teams will be sorted by their Ranking Score. Note: Because your Ranking Score is derived directly from the Match Scores of the losing alliances in the matches you play, it is in your best interest to support your opponents and win by helping each alliance score as many points as possible.

• If any teams within a Tier have the same Ranking Score, they will then be sorted by their Highest Match Score.

• If any teams within a Tier have the same Ranking Score and the same Highest Match Score, then the Scoring System will sort those teams based on a random electronic coin toss.

Joe Ross

17-03-2005, 13:24

Your UPDATE15 and MORELLA variables are reversed. (ie, checking update15 causes the morella method to be used).

Edit: I've checked the QP calculations for the update 15 method and they match mine and SOAP's.
Edit2: The Morella method looks good too.

Joe Johnson

17-03-2005, 16:09

Joe Johnson

17-03-2005, 17:05

Hi all, I have made a new version.

It fixes the Morrella/Update15 mix up and adds the option to sort by highest QP or Highest Match Score.

I think it will work but who knows. Please test and let me know

Send me a short message on my pager: Reversed for my spamming protection
moc.liamriaym@6716584685

Here is the file. (http://www.chiefdelphi.com/forums/papers.php?s=&action=single&paperid=471)

Joe J.
NOW MY PAGER SCREWS UP!!!!

I got a page from someone but it was too garbled to read.

If someone had feedback that I need to know, please post it here AND send a short note to my pager again. I will try to reply via my pager to confirm that I got it.

Thanks.

Joe J.

Kevin Sevcik

17-03-2005, 20:23

Update: The spreadsheet works in OpenOffice.org 2.0 Beta for those interested. The OFFSET thing was apparently a documented issue in how OOo 1.1.4 handled things and was fixed for 2.0

Joe Johnson

17-03-2005, 20:38

Kevin, David, Joe, & Karthik,

THANK YOU THANK YOU THANK YOU.

I am going to ship Version C to Jason Morrella for folks to use as a "proven" backup (per Paul Shay's e-mail) around the country at the regionals this weekend.

I believe that it does what it is supposed to do.

Could not have done it without all your help.

Thanks again.

Joe J.

Kevin Sevcik

17-03-2005, 20:38

Just noticed something fiddling in OO with the spreadsheet. I don't think there's a ruling from FIRST on this, and it will rarely be important, buuut...

Currently a Surrogate team has its entry removed from the Tie Breaker column. However, a DQ'd team still gets the high score from that match. Logic would suggest that a DQ'd team not benefit from the match and not have it counted towards their high score. The formula to implement this in cell T2 on the MATCHDATA sheet would be:

=IF(OR(F2="X",E2="X"),"",IF(N2="X",D2,""))

I think. That's what it is in OOo, except with semicolons instead of commas. Then fill that down the column.

EDIT: And posted seconds after poor Joe thanks me. Don't I feel silly now...

Joe Johnson

17-03-2005, 20:41

OKAY... I will Make a NEW rev (D) and SHIP THAT. HAPPY?!? ;-)

Joe J.

Kevin Sevcik

17-03-2005, 20:43

Quite so. :D I'll just close OO now and not open it again for a week or so....

Seriously though, thanks for all the hard work. Nitpicking little bugs is way easier than wading through some of those formulae in there.

Joe Ross

17-03-2005, 21:33

Currently a Surrogate team has its entry removed from the Tie Breaker column. However, a DQ'd team still gets the high score from that match. Logic would suggest that a DQ'd team not benefit from the match and not have it counted towards their high score.

Interesting, I thought I had checked that.

Joe Johnson

17-03-2005, 21:57

Joe and Kevin,

Kevin, I made the change a bit different than you suggested. I zeroed their score rather than taking them off the TeamNoExt list -- the first gives them a zero High Score for that DQ's match, the second is like they never played the match (which would screw up the other calculations - win loss record, etc.)

Joe, you probably check the program when it used the highest QP points rather than the highest MATCH Score for tie breaker 3.

Thanks again.

Again, if you will please check it one more time, I would be grateful.

The new one is HERE (http://www.chiefdelphi.com/forums/papers.php?s=&action=single&paperid=472).

Joe J.

Karthik

17-03-2005, 22:30

Not so much a bug, rather a question of implementation. The final tiebreaker in the standings is the "random electronic coin toss", and you are using the Rand() function to generate this number for all teams.

The problem I noticed is that Rand() recalculates after every data refresh. So, while two teams are tied, their ranking will fluctuate each time new data is inputted. Perhaps this column should be made static at the initial entry of all team data? This way these fluctuations would be eliminated.

Thoughts?

Kevin Sevcik

17-03-2005, 22:46

Honestly. I really am banning myself from checking this after this one. I'm finding pathological cases purely by accident now. However, if you are in Highest MS mode and a team gets DQ'd two rounds in a row, their Tiebreaker.. erm.. breaks. Again, I dunno if this is just OO, but I suspect not. I traced it to the Score column. If a team was DQ'd their score was set to "0". A text zero. Presumably OO atleast doesn't like comparing the two text zeroes resulting from two DQs in a row.

The new formula for MATCHDATA.M2 (pregnant women and those with heart conditions avert your eyes):

=IF(E2="X";0;IF(OR(B2=1;B2=2;B2=3);OFFSET($DISPLAY_ENTRY.Q$1 :Q$65536;MATCH(C2;$DISPLAY_ENTRY.C$1:C$65536;0);0; 1;1);IF(OR(B2=4;B2=5;B2=6);OFFSET($DISPLAY_ENTRY.Q $1:Q$65536;MATCH(C2;$DISPLAY_ENTRY.C$1:C$65536;0)+ 1;0;1;1);"-")))

Change is in bold. I swear I'm going to bed now, Joe.

EDIT: I did some cursory searches for "0" on the other sheets and didn't turn anything up, so this is hopefully the only spot.

EDIT2: I forget I pasted this straight from OO. All those semicolons should be commas for those keeping score at home. Heh.

Joe Johnson

17-03-2005, 23:00

Kevin,

IS THAT ALL YOU GOT??? IS THAT ALL YOU GOT??? ;-)

I am not going to even check it. I suppose it is true. I really klugged this thing together. The highest point business was not in my thought process when I started down this path. After I was almost done, some thoughts about a need for something other than a random coin flip was brought up. I really have not read the rules this year... ...anyway, I noodled through a work around but I really don't like it. The bug you discuss is probably from that work around.

If we actually NEED a bulletproof back up next week, I'll rewrite it with some VB code or pivot tables or one of a 1000 other better ways to make it happen.

Cross your fingers that we won't need it.

Joe J.

Kevin Sevcik

17-03-2005, 23:10

I have defeated the great Joe Johnson through perversity and sheer bloodymindedness. Surely there's some reward there.

But yes. I think the current spreadsheet is near bulletproof enough to work very well. I just wandered across that and felt the need to investigate. And I do hope things run perfectly at the regionals and this need never come up this week or next. And now I'm off to bed where I can do no further damage....

Karthik

25-03-2005, 12:52

Hey guys,
Here's a vb script that takes the match data from Hatch's FIRST Scoring system, and generates a line by line file that can be used with Dr. Joe's spreadsheet. Further instructions are in the file, which can be executed on any windows computer. To edit the file, just open it up in notepad.

Note: My virus scanner detected the script as malicious since it writes to a file, but you can ignore that error.

This script was generously created by Waterloo and Toronto scorekeeper Lambo Jayapalan.

Enjoy...

Btower

26-03-2005, 20:42

A note of Caution!!
At this point, I suggest using the Excel sheet as as independent backup to the Hatch system. By this I mean entirely separate. If it downloads from the Hatch system, the audit trail will be lost. At Detroit we found scoring errors through comparision o the online system to our entries and the rankings in Dr. Joe's multicolored, magical mystery sheet. If we had downloaded results, I don't believe we would have noticed the errors.

I have not used this week's Hatch version, so my concerns may no longer be warranted. :)

Ben Tower
05 GLR & Detroit Field Manager

Joe Johnson

29-03-2005, 11:16

A newer version (http://www.chiefdelphi.com/forums/papers.php?s=&action=single&paperid=479) has been made to address some suggestions from Walt at FIRST.

Please help debug it if you can. The changes were fairly simple, but you never know when you introduce new bugs.

Help if you can.

Thanks.

Joe J.