Ok I think I created something that works.
You can check it out by downloading here:
http://cphl.calgarypuck.com/pages/si...L_Lottery.xlsx
I would appreciate a few people checking my work.
Here's how it works
There are two draws. One for the first and one for the second overall pick.
So start with the 1st overall tab. Each cell represents a lottery ball, with each team getting a certain number of lottery balls based on the odds of that team getting the 1st overall pick as outlined here:
So Buffalo was last overall in the NHL, mapping to NJD in the CPHL. Those teams have a 16.6% chance of 1st overall so they get 166 lottery balls. The second last place team has a 12.1% chance so they get 121 balls. This would theoretically add up to 1000 total balls in the first round lottery but doesn't for us because Seattle is not included.
Cell B4 pulls one of the balls/cells randomly. This cell re-freshes anytime you change any part of the worksheet so you need to record the number. For instance I pulled #235 which maps to NYI in the CPHL. (note this designate the original team for the pick, not who may own the pick now through trades). This means the NYI pick would be first overall in the CPHL.
I then go to tab 2 - and before I run the second round I first half to remove ALL the NYI balls from the lottery. Once that is done the cell will automatically pull another ball which is your 2nd overall pick, which for me was 649 - Washington. This means the caps pick is the #2 pick.
From there the draft just carries along in reverse standings.
So it would be
1. NYI
2. Washington
3. NJ
4. CBJ
5. Tampa
6. Carolina
7. Calgary
8. Florida
9. Montreal
10. Toronto
11. Dallas
12. St. Louis
13. Ottawa
14. Vancouver
15. LA
Again those aren't the teams that own the pick - those are the original owners of each pick.
Please take a look at the file and let me know if anyone has any concerns but this should work for us. We would run the lotto live with witnesses as we do with the sim site.