[arod23]

Draft Lottery Calculator
v.1.0

In one of my leagues, we decided to add a little excitement to the draft process and at the same time reduce the incentive for GMs to tank the last games of the season to finish in last place in order to secure the first draft pick.

Like the NBA, we devised a lottery system for the first round of the draft. The last place team still has the best likelihood to have the first pick but it is no longer a guarantee. And, now there is a chance (albeit slim) that a better team could draft higher than their record dictates.

We designed an Excel spreadsheet to automate this task. It is set up for 16 teams but less or more could be accommodated. In a large league, say 30 teams or so, we typically only include the worst teams as eligible to participate in the lottery, e.g., the last two teams in each division, or the worst 16 teams, or the division losers, etc. The teams not eligible for the lottery are just ordered normally by record after the lottery teams.

We have included a user-specified Adjustment Factor to increase the probability of the worst teams being awarded the early picks. The lower the factor, the lower the probability of the worst teams receiving an early pick. Conversely, the higher the factor, the higher the probability of the worst teams receiving an early pick. You can see how changing the factor changes the probabilities shown in column I.

In the spreadsheet example, I used 5 as my factor. I also include a mix of records just as an example. With about a one in 20 chance, the #7 team ended up getting first pick. The worst team received a pick in the top-5. And, the best team got the 3rd to last pick instead of the last pick.

This is not a simple "sample without replacement" task like the PowerBall Lotto. The chance of a team being "drawn from a hat" is not equal but based on an underlying discrete probability function. This is why Excel's sampling function cannot be used.

Instructions on how to use it are on the spreadsheet itself. It is not necessary to enter the teams in descending order of wins, but it does make it easier to see who moved up or down compared to the normal draft order. It can also accommodate any season length schedule since it is based on Win%.

Lastly, the spreadsheet does NOT contain any macros, so choosing the safer "disable macro" option when opening will not affect it. The random number generator can take some time depending on your PCs speed and memory available. It may appear to freeze but give it some time. You should only have to generate 200 numbers so it should take less than a minute. The more you generate the longer it takes.