paper: 2013 MAR Champ Clinch Berth

[The Lucas]

Originally Posted by AGPapa

I wanted to do similar calculations, but I couldn't figure out how to; it is interesting to see how you did it. I have a question regarding the row titled "Count of teams needed to tie #". How did you calculate this? Thanks.

I am assuming the CA goes to a very low ranked team that doesn't even figure in my points calculations (an unlikely or impossible assumption, but I am calculating the worst case scenario & I don't now who is submitting). Thus the 50th place is the last team out, since DCA auto qualifies. So the Current Rank + "Count of (ranked below) teams needed to tie #" = 50 in most cases. For instance, 28 teams below Team 2539 (rank 22) would have to tie their point total to form a 29-way tie for 22nd rank and one team would be out (I ignore tiebreakers because that would be very complex, and a 29 way tie is highly unlikely to impossible). This would require 1114 points, which is more than exist in MAR, so they clinch. Additionally, since I am ignoring tiebreakers (assuming the current list is not tiebroken correctly), teams that are currently tied need less teams below them to tie.

In practice how I do this in Excel is:
-I copy values for teams ranked 20-49 and Paste Special "Transpose" into Cell P114.
-"Count of teams needed to tie #" started as a simple descending counting series (30 to 1 in this case). I go down the columns looking for ties. When I see one I copy the rightmost value into all the tied teams cells (simple drag).
-Then I manually delete cells in the above matrix of formulas till "Count of teams needed to tie #" equals "Count of teams in sum". A Macro could be made to do this and other tasks, but I don't do it very frequently.

I hope to update this Sat Night, but since I a vol at Bridgewater I will not be able to update more frequently. If you would like to build something like this into your Gdoc, please do so and ask any questions you need.