Chief Delphi

Chief Delphi (http://www.chiefdelphi.com/forums/index.php)
-   Extra Discussion (http://www.chiefdelphi.com/forums/forumdisplay.php?f=68)
-   -   White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams (http://www.chiefdelphi.com/forums/showthread.php?t=36282)

Joe Johnson 17-03-2005 03:44

White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams
 
Thread created automatically to discuss a document in the White Papers.

Spreedsheet to Calculate the Ranking of Teams by Joe Johnson

Joe Ross 17-03-2005 04:06

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

Joe Ross 17-03-2005 06:00

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.

Joe Johnson 17-03-2005 06:57

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

Originally Posted by Joe Ross
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...

Quote:

Originally Posted by Joe Ross
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

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

Originally Posted by Joe Ross
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

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?

Joe Johnson 17-03-2005 09:44

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.

Joe Ross 17-03-2005 11:39

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

Originally Posted by Joe Johnson
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

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.

Joe Johnson 17-03-2005 13:04

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

Originally Posted by Kevin Sevcik
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.

Joe J.

Karthik 17-03-2005 13:21

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

Originally Posted by Joe Ross
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.

Quote:

Originally Posted by 2005 Manual, Section 8.3.7

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

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.

Joe Johnson 17-03-2005 16:09

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.

Joe Johnson 17-03-2005 17:05

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

Originally Posted by Joe Johnson
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.

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

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


All times are GMT -5. The time now is 14:49.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright © Chief Delphi