White Paper Discuss: Spreedsheet to Calculate the Ranking of Teams

[Joe Johnson]

Thread created automatically to discuss a document in the White Papers.

Spreedsheet to Calculate the Ranking of Teams by Joe Johnson

[Joe Ross]

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]

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]

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

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]

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]

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

[Joe Johnson]

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]

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]

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]

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]

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.

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]

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]

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]

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]

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