17-03-2005 04:06

Joe Ross

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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

17-03-2005 06:00

Joe Ross

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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.

17-03-2005 06:57

Joe Johnson

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

17-03-2005 06:59

Joe Johnson

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

17-03-2005 09:27

David Kelly

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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

17-03-2005 09:44

Joe Johnson

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

Use the New one HERE.

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.

17-03-2005 11:39

Joe Ross

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

17-03-2005 11:43

Kevin Sevcik

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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.

17-03-2005 13:04

Joe Johnson

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

17-03-2005 13:21

Karthik

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

17-03-2005 13:24

Joe Ross

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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.

17-03-2005 16:09

Joe Johnson

Hopefully LAST version. 2005-03-17c

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.

Joe J.

17-03-2005 17:05

Joe Johnson

Re: Hopefully LAST version. 2005-03-17c

17-03-2005 20:23

Kevin Sevcik

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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

17-03-2005 20:38

Joe Johnson

Ship it!

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.

17-03-2005 20:38

Kevin Sevcik

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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...

17-03-2005 20:41

Joe Johnson

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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

Joe J.

17-03-2005 20:43

Kevin Sevcik

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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.

17-03-2005 21:33

Joe Ross

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

17-03-2005 21:57

Joe Johnson

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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.

17-03-2005 22:30

Karthik

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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?

17-03-2005 22:46

Kevin Sevcik

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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.

17-03-2005 23:00

Joe Johnson

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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.

17-03-2005 23:10

Kevin Sevcik

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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....

25-03-2005 12:52

Karthik

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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...

26-03-2005 20:42

Btower

Re: White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

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

29-03-2005 11:16

Joe Johnson

2005-03-29 Revision

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.