White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

[Joe Johnson]

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]

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]

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

Joe J.

[Kevin Sevcik]

Quite so. 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]

Originally Posted by Kevin Sevcik

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]

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.

Joe J.

[Karthik]

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]

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]

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]

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]

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]

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]

A newer version 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.