Generating Lottery Pick Worksheet

[Naas]

Hi, Everyone.

I am the commissioner of a Fantasy Hockey league. At the end of the season, the lowest 7 players will have an opportunity to be placed in a draw to determine the drafting order for the following year. The player that finishes last will have the highest odds of getting the #1 pick, followed by the second last finisher, and so forth.

I realize this can be done for the first pick by using discrete probabilities.

The problem I am encountering, however, is that I want to generate the order for all 7 picks in one swoop. Each players odd's will differ (for instance, the 4th last player's offs of moving up to 1st overall will be 10%, and there's also a 10% chance that he can move down to 7th overall), and the picks have to be discrete (meaning that only one player gets the 1st overall pick, only one player gets the 2nd pick, and etc).

Essentially, if you're familiar with the NBA Draft Lottery, that's what I'm trying to build, but I have no idea how. Any input is appreciated.

 

[shg]

What are the assigned odds for each player?

 

[Naas]

That I haven't determined yet, and will be selected on a unanimous basis. I'm hoping to develop the framework and input the numbers later, if that's possible.

At the end of the day, one run of the model should provide the pick order from 1-7 on the basis of their individual chances of moving up and down, which are based on how they finished.

 

[shg]

Hmm.

There are =FACT(7) = 5040 ways that could results, and there is some probability of each. So you could list all the outcomes (easy), and compute the probability of each outcome based on the individual probabilities (that part's not jumping out at me). Those probabilities must sum to 1.

So convert that list of discrete probabilities to a list of cumulative probablities, and you can index that list with a single random number to get all 7 results.

 

[Naas]

That's a good start - thank you!

 

[shg]

OK, figured that out, though it's not pretty. There's a workbook with an example for a 5-way draft http://www.box.net/shared/mc40pshy2e5jcpukxnrx. One random number picks an outcome.

As a participant, though, I'd be skeptical of this (especially if I were in charge); it's too simple. The NBA draft uses many rounds of picking ping-pong balls.

A mechanical aternative is this:

Get a bunch of marbles in 7 colors. Assign one color to each player. The lowest ranking player gets, say, 50 red marbles, the next gets 25 blue marbles, the next gets a dozen green marbles, ...

All those marbles go into bowl (or, ideally, an urn!), and you pick marbles blind until one of each color has emerged. The order of appearance of each color is the draft order.

 

[shg]

I added your seven-player draft, same link.

 

[Naas]

Shg, thank you so much. You've been a great help.

 

[shg]

You're welcome, it was an interesting exercise that I will reuse for classes.