Deciding the Draft Order

Do you know how much of a pain in the ass it is to carry around 30 ping pong balls or drawing out pieces of paper, cutting them apart, folding them, throwing them into a hat and then going around the room drawing names?  Well, sure... you didn't have to do those things, as you sat patiently, waiting for your turn to draw a number.  Let me tell you, it was a pretty big pain in the ass.

Last year, I devised a system, which derived from the random number function in Excel and took into account the results of the year before and to my surprise, I didn't catch my flack for that, rather, I thought it worked okay, despite taking forever to finish.

This year, I'm working on the same idea, but have certainly made the process a little bit better and I thought I would share the process, now two weeks before the draft, just so there isn't too much surprise, unless you didn't read the blog beforehand.

First things first, I have a master team list in my Excel worksheet, which has everyone who has played in the pool, since it became live on opiatedsherpa.com.  That's already eight years worth of teams and there is already the expectations of some new teams joining us for year number nine on the website.  As of the end of last year, I have had 48 people/teams in the hockey pool, which is a lot of standings to draw from.

The weighting system is kind of straight-forward, as it gives people/teams a better chance at a higher 1st round pick for your previous results.

First, it takes into consideration last year's pool results, as recent memory weighs heavy on the regulars in the pool.  The position in the standings, immediately acts as your weight in the random number process.  Kristy & Don will have a weight of one at draft order selection, while Wyllie, if he is to return to the pool this year, will have a weight of 23.  This will be important in a moment.

Second, if you're returning to the pool after an absence, of no matter how many years, you will then get the weight of your average results in the pool.  For instance, if Chris M. was to return to the draft this year, he would get the average of his previous six years worth of results, which would give him a weight of nine, when the order gets decided.  Cindy S., who played only once, back in 2014, would get her only result as her weight, a 13.

Finally, if you're new to the draft, you would get a weight, much like expansion teams to the NHL.  You wouldn't get the best weight in the draw, but you would receive something close, which would give you a pretty good shot at a high pick, even the 1st pick, if you're craving a top end goalie like Carey Price.

Once everyone is at the draft and accounted for, the list of names and their respective weights, gets moved into a random number list.

The Excel random number function (=RAND()) will provide you a new number between 0 and 1 in a decimal form, giving you upwards of nine decimal places, for every function done in Excel.  Every time you perform another function in Excel, the number in every cell with the random number function will change to something new.

With that being said, the list of names, include the random number function multiplied by the weight assigned by the previous results.  Given that the number comes in all kinds of different decimal places, there is a chance, no matter how remote it may be, that Kristy & Don could finish with the 1st overall pick, despite finishing 1st last season.  Very, very remote.  Rather, their number can only top out at one, while Wyllie's can finish as high as 23 (or thereabouts).

Once the list is set to go, I will hit a macro button, which kicks off by setting off a counter, which warms up the random number generator, and then once it is ready to go, it sorts the team list by the random number * weight in a descending order, taking the lowest team on the list and placing it on the list in the order that they are taken off.  Every time a function goes off, a new random number is applied to each team, the list is sorted again and the bottom team is taken off for the next pick.  This goes until all the teams have been picked from the list, thus providing us with the draft order.

Once the macro button is pushed, it only takes a matter of seconds to determine the order, which is super-helpful and there may be a sense of justice for those previously in the pool, at the same time.  The worst teams last year get the best chance to get the best picks in the new year, while new teams get a good chance to compete right away and teams that won the year before, are challenged to be good again through a tougher 1st round pick.

Of course, there are some people who don't want the 1st pick overall, because it's a long time before you get to your 2nd round pick, in the snake format, but that's the way it goes.  It all averages out in the long run.

The code will be available for those who are curious and for the sake of transparency.  Just let me know.