Thread created automatically to discuss a document in CD-Media.

2013 MAR Champ Clinch Berth by: The Lucas

Spreadsheet to calculate of which MAR teams have clinched a berth in the MAR Championship after Week 4

Similar to what I did last year, teams that have Clinched a berth in the 50 team MAR Championship are highlighted in green. Chairman’s Award Auto Qualifiers are highlighted in gold (all in the top 50 currently). Here is my Methodology (also found in the bottom right of the spreadsheet):
-Calculate the points remaining in MAR
-Assume the Bridgewater Chairman (Auto Bid) is ranked 94 or below (not impacting other points calculations). Chairman’s winner will essentially take the 50th spot so everyone else has to be 49+
-Next for each team in the rankings (starting at 20), calculate the minimum number of pts needed for enough Bridgewater teams to tie them in the rankings to possibly force that team out of MAR Championship on tiebreakers
-I am not considering tiebreaker ordering for teams with the same number of points, which is why I have currently tied teams with the same number of teams needed to tie them to knock out

Go ahead and use this thread for error reporting/feedback. I might be able to update this after Sat of Bridgewater (I will be vol as an RI). If someone wants to make a Google Doc to live update like last year, I will help as much as I can

Anyway, there are only 949 points remaining, not 997. There are only 42 teams at BR (not 43) and three already competed in two districts (and are ineligible for points). This would mean that teams 25 and 56 should be a lock for MAR Champs.

Additionally, I expect at least 4 highly ranked MAR teams to decline their invitation to MAR Champs:
341 - Rank 1: Going to Las Vegas previous week
103 - Rank 6: Declined last year, see no indication they will attend this year (other than not registering for a regional)
365 - Rank 10: Going to Chesapeake same week. Student council voted for Baltimore with only 1 dissenter (our driver wanted the competitiveness of MAR Champs)
25 - Rank 24: Going to Las Vegas previous week

If that is the case (or if just 3 decline) that means 56 and 1367 clinch.

If you are a team on the bubble and want a rooting interest, root for these teams to accumulate as many MAR points as possible (as they will not push teams currently in the Top 50 out):
In Top 50 after 1 event:
11
56
1676
75 CA
224
3rd District Teams:
303
1279
3314

The tendency is for the top teams to continue to accumulate high point totals in their 2nd event (not surprising). Expect them to cause many other teams to clinch Sat by winning Qual Matches.

Thanks for bring that thread to my attention, I added the MAR Sub-Forum to my portal :o I actually did these calcs as soon as the MAR Rankings came out, but delayed putting it out till today (was waiting for the teams to be set at 50 teams since it also said 60 teams in MAR rules). I figured Scott would put out the Gdoc again this year.

I didn’t see that 709 officially dropped Bridgewater. However, I expect 4128 to take their place. I was helping them all of Chestnut Hill, and got them to submit for the wait list for Brigdewater since they had to miss Mt. Olive for a choral concert (or something similar). I confirmed this switch was pending last week.

With 4128, there is 997 MAR points left (without 985). The 3rd event teams do create MAR qualifying points. Qualifying points are a pool of all non-surrogate match slots at an event. The winners take all (the 3rd event teams could conceivably lose all their matches, thus not destroying any of those points).

Thank you for the explanation, I had forgotten that the qualification points is a function of the number of matches, not the number of teams.

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.

New version with updated standings. Congrats to 56, 1676, and 25 for clinching berths to MAR Champs

I updated the total points with simple copy > paste > formula > sort (in NJBRG tab), if you notice any errors please tell me. I only up dated the total points column, the other columns for active teams are unchanged (like most wins). I improved how I handle teams above Chairman’s teams that haven’t clinched on points.

I have some formulas prepared for the draft. I will update during lunch if we get everyone Elim reinspected in a timely manner (start building those blockers now )

After the draft, the picture should become much clearer since only 245 Elim and award points will be left. I plan on only considering the 24 teams drafted in the points calculation, ignoring any backup teams. It is possible for a backup team to get significant points but it is rare (elim points are shared with team they are replacing in proportion to matches won by each IIRC).

Ok for v2.1 here is my rather hasty attempt to calc who has clinched after draft, while Inspecting and watching matches. I prob made some mistakes, fell free to correct me, repost etc.

Assumptions:
No backups, so teams not drafted can only get 5 pts from one award.
If more teams are needed to tie a team then can possible get that amount of points (aka most are eliminated) then they clinch.
555 & 4475 are the only ones of these that have a chance at the top 50.
Chairman’s can still be anyone.
I didn’t account for who is on what alliance, everyone cant win it all

So the last 3 to clinch at draft time point are 1495, 1143 & 1881 with 52pts since 555 & 4475 can only get 47 pts with a single award each.