ECAC Byes and Home-Ice 2011-12

[burgie12]

Re: ECAC Byes and Home-Ice 2011-12

OK, I've done my own (somewhat brute force) spreadsheet which gets every number right (I think). I think it's easier to use than yours because all you have to do is change the KRACH numbers and the whole spreadsheet updates without any macros or sorting. So, when a team is eliminated, all you have to do is set their KRACH to 0. Let me know what people think.

That's pretty kick-*****. I got lost somewhere in the middle, but I'll look at it again later when my head stops spinning.

Sad note of the day: Despite the fact that I use VLOOKUP quite often, I didn't know about the existence of the HLOOKUP formula.

But, seriously, thank you for the spreadsheet. I will probably steal it for next season.

 

[burgie12]

Re: ECAC Byes and Home-Ice 2011-12

Alright, so I lied when I said I knew why I wasn't getting the percentages that I was expecting. I completely remade my spreadsheet, using goblue's as a starting point and it has been re-uploaded and is now fantastically more accurate. It also should be easier to use and follow. It's been completely remade, but still has the same file name, so it has the same download link.

A quick tutorial for those looking to give it a whirl: You can adjust the KRACH values to any positive value that you wish. Percentages will auto-update (unless you have turned off automatic calculations). There is a dropdown box for each team so you can specify when they are eliminated. I've tried to make it intelligent, but I didn't try too hard. So, you can't tell it that five teams lost in the 1st Round because it knows that there were only 4 series. But, you could tell it that both #8 SLU and #9 Dartmouth were eliminated in the 1st Round and it would never stop you, but then all of the percentages / odds in the rest of the spreadsheet will go to 0.

If you leave the macros disabled, it will be unable to do that double-checking and there will be some other unintentional mishaps, but no different than telling it that both #8 and #9 lost in the 1st Round.

CAUTION! Choosing an option in the dropdown box will cause the document to go through most (nearly all) of the worksheets and hide all of the non-important rows / columns (basically, anything where the odds are 0). This is a very slow macro (it took about 1.5 minutes when I ran it just before I uploaded it). So, if you're going to go through and make wholesale changes to who survived what round just to screw around and see what happens, you should probably disable macros first.

Anyways, here's a reminder on "your team's" odds going into the weekend using KRACH values that are current as of last night's PC-UML game.

--------------------------------------------------------------------------------
| # | Team         |   1st  |Quarter | Semi  | Final |  Win  |||Consol |  3rd  |
--------------------------------------------------------------------------------
| 1 | Union        |   Bye  | 100.00 | 90.22 | 63.04 | 38.84 ||| 27.18 | 16.94 |
| 2 | Cornell      |   Bye  | 100.00 | 77.58 | 46.48 | 23.46 ||| 31.09 | 17.56 |
| 3 | Harvard      |   Bye  | 100.00 | 64.30 | 29.70 | 12.84 ||| 34.60 | 17.32 |
| 4 | Colgate      |   Bye  | 100.00 | 49.81 | 20.21 |  8.56 ||| 29.60 | 14.37 |
| 5 | Quinnipiac   | 100.00 | 100.00 | 50.19 | 20.42 |  8.68 ||| 29.77 | 14.49 |
| 6 | Yale         | 100.00 | 100.00 | 35.70 | 11.63 |  4.33 ||| 24.06 | 10.07 |
| 7 | Clarkson     | 100.00 |   0.00 |  0.00 |  0.00 |  0.00 |||  0.00 |  0.00 |
| 8 | St. Lawrence | 100.00 |   0.00 |  0.00 |  0.00 |  0.00 |||  0.00 |  0.00 |
| 9 | Dartmouth    | 100.00 | 100.00 | 22.42 |  6.01 |  2.44 ||| 16.41 |  6.78 |
|10 | Rensselaer   | 100.00 | 100.00 |  9.78 |  2.49 |  0.84 |||  7.29 |  2.47 |
|11 | Princeton    | 100.00 |   0.00 |  0.00 |  0.00 |  0.00 |||  0.00 |  0.00 |
|12 | Brown        | 100.00 |   0.00 |  0.00 |  0.00 |  0.00 |||  0.00 |  0.00 |
--------------------------------------------------------------------------------
|   |              |    8   |    8   |   4   |   2   |   1   |||   2   |   1   |
--------------------------------------------------------------------------------

 

[goblue78]

Re: ECAC Byes and Home-Ice 2011-12

Nicely done, and well organized. As you can tell, organization is not my strong point.

 

[goblue78]

Re: ECAC Byes and Home-Ice 2011-12

One more suggestion, inspired by last night. Add another switch that will allow you recalculate after the first game, and, if needed, second game of a 2-out-of-three, so that the team up 1-0 has a probability of p+(1-p)p of moving on (where p is its probability of winning one game), while the team down 0-1 is (1-p)^2 to advance, or, in the latter case, turns the series into a one-game advancement.

 

[burgie12]

One more suggestion, inspired by last night. Add another switch that will allow you recalculate after the first game, and, if needed, second game of a 2-out-of-three...

It's on my wish-list. If next weekend was another 2-of-3 series then I would definitely finish it up this weekend or early next week. Since it's not, I'll probably focus on adjusting this for the NCAA Tournament and deal with making this spreadsheet capable of recalculating in the middle of a weekend (along with the changes I want to make to the floor/ceiling script) during the off-season.

 

[burgie12]

Re: ECAC Byes and Home-Ice 2011-12

Updates going into Game 3 of the Quarters with KRACH values that include all of Saturday's results. Colgate is actually favored over Quinnipiac now, so extra good luck to both teams.

Oh, and another disclaimer about the spreadsheet. I'm assuming that there's no tie in the consolation game. That's not true, but, oh well.

---------------------------------------------------------------------------------
| # | Team         |   1st  |Quarter |  Semi  | Final |  Win  |||Consol |  3rd  |
---------------------------------------------------------------------------------
| 1 | Union        |   Bye  | 100.00 | 100.00 | 69.94 | 42.29 ||| 30.06 | 18.79 |
| 2 | Cornell      |   Bye  | 100.00 | 100.00 | 60.42 | 29.99 ||| 39.58 | 22.94 |
| 3 | Harvard      |   Bye  | 100.00 |  58.67 | 23.79 |  9.57 ||| 34.88 | 16.27 |
| 4 | Colgate      |   Bye  | 100.00 |  50.23 | 17.48 |  7.10 ||| 32.75 | 15.14 |
| 5 | Quinnipiac   | 100.00 | 100.00 |  49.77 | 17.22 |  6.95 ||| 32.55 | 14.97 |
| 6 | Yale         | 100.00 | 100.00 |  41.33 | 11.16 |  4.10 ||| 30.18 | 12.90 |
| 7 | Clarkson     | 100.00 |   0.00 |   0.00 |  0.00 |  0.00 |||  0.00 |  0.00 |
| 8 | St. Lawrence | 100.00 |   0.00 |   0.00 |  0.00 |  0.00 |||  0.00 |  0.00 |
| 9 | Dartmouth    | 100.00 | 100.00 |   0.00 |  0.00 |  0.00 |||  0.00 |  0.00 |
|10 | Rensselaer   | 100.00 | 100.00 |   0.00 |  0.00 |  0.00 |||  0.00 |  0.00 |
|11 | Princeton    | 100.00 |   0.00 |   0.00 |  0.00 |  0.00 |||  0.00 |  0.00 |
|12 | Brown        | 100.00 |   0.00 |   0.00 |  0.00 |  0.00 |||  0.00 |  0.00 |
---------------------------------------------------------------------------------
|   |              |    8   |    8   |    4   |   2   |   1   |||   2   |   1   |
---------------------------------------------------------------------------------

 

[burgie12]

Re: ECAC Byes and Home-Ice 2011-12

Good luck to all in the final weekend.

Both semis have pretty clear favorites while the potential Saturday match-ups are a little closer.

-----------------------------------------------------------------
| # |   Team  | Final |  Win  \  2nd  |||Consol |  3rd  \  4th  |
-----------------------------------------------------------------
| 1 | Union   | 66.61 | 39.85 \ 26.76 ||| 33.39 | 20.42 \ 12.97 |
| 2 | Cornell | 58.32 | 28.70 \ 29.62 ||| 41.68 | 22.87 \ 18.80 |
| 3 | Harvard | 41.68 | 17.14 \ 24.54 ||| 58.32 | 27.25 \ 31.08 |
| 4 | Colgate | 33.39 | 14.31 \ 19.08 ||| 66.61 | 39.46 \ 37.15 |
-----------------------------------------------------------------