Draft Lottery Calculator

arod23 · 12-21-2005, 04:05 PM

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.

Jeremy Weimer · 01-04-2006, 03:25 AM

I know this is old, but I have to ask a question. How do you get the order if you're using Open Office and not Excel? I assume the 2nd column of instructions are how you generate the order, but I don't have those options in Open Office. There is no Data Analysis under the Tools window. So what exactly am I supposed to do? Or is this not going to work right with Open Office?

Tools \ Data Analysis
Random Number Generation
1 = Number of Variables
200 = Number of Random Numbers
Discrete = Distribution
Select red highlighted area, do not include 0% probability cells
Leave Random Seed Blank
$Q$8 = Output Range
OK
OK to overwrite

These are the instructions on how to get the order, right?

arod23 · 01-04-2006, 08:47 AM

You need to be able to run a Random Number Generator based on a Discrete Probability Function. That is one of the function included in Excel's Data Analysis Tool. I don't know if Open Office has that function or if it is an add-on. (Note, the standard random number generator won't help you here).

Sublimity · 01-04-2006, 08:55 AM

Now that this has been bumped, I'd like to say this is really cool. Very good work on it.

Jeremy Weimer · 01-04-2006, 03:24 PM

Quote:

Originally Posted by arod23

You need to be able to run a Random Number Generator based on a Discrete Probability Function. That is one of the function included in Excel's Data Analysis Tool. I don't know if Open Office has that function or if it is an add-on. (Note, the standard random number generator won't help you here).

****. Anyone know if it's possible to use this in Open Office?

Jeremy Weimer · 01-05-2006, 06:31 PM

Anyone at all?

arod23 · 01-05-2006, 06:53 PM

OpenOffice doesn't appear to have that function preprogrammed but you might be able to create a macro to do it. Here is an example of how to create a random "normal" distribution using the rand() function.

http://www.openofficetips.com/blog/a...m_numbers.html

The discrete probability is a different formula though.

Jeremy Weimer · 01-05-2006, 06:56 PM

Quote:

Originally Posted by arod23

OpenOffice doesn't appear to have that function preprogrammed but you might be able to create a macro to do it. Here is an example of how to create a random "normal" distribution using the rand() function.

http://www.openofficetips.com/blog/a...m_numbers.html

The discrete probability is a different formula though.

So basically without Excel I'm kinda f00ked.

Spindel · 01-06-2006, 12:42 AM

Yep, pretty much.

Don't worry, I'd love to use this baby myself, too. So you aren't the only one.

jf7623 · 01-08-2006, 10:14 PM

awesome tool

01-04-2006, 08:55 AM	#4
Sublimity Hall Of Famer Join Date: Oct 2005 Location: This thread. Posts: 3,199	Now that this has been bumped, I'd like to say this is really cool. Very good work on it. __________________ mrs ria: I hereby dub Sublimity the Glorious Upholder of the 5B3. Current leading vote-getter in the Worst Poster in OT History poll.

01-06-2006, 12:42 AM	#9
Spindel All Star Reserve Join Date: Oct 2003 Location: NJ Posts: 763	Yep, pretty much. Don't worry, I'd love to use this baby myself, too. So you aren't the only one. __________________ Union League Baseball Commissioner

01-04-2006, 03:25 AM	#2
Jeremy Weimer Hall Of Famer Join Date: Jun 2003 Location: Minneapolis, MN Posts: 3,411	I know this is old, but I have to ask a question. How do you get the order if you're using Open Office and not Excel? I assume the 2nd column of instructions are how you generate the order, but I don't have those options in Open Office. There is no Data Analysis under the Tools window. So what exactly am I supposed to do? Or is this not going to work right with Open Office? Tools \ Data Analysis Random Number Generation 1 = Number of Variables 200 = Number of Random Numbers Discrete = Distribution Select red highlighted area, do not include 0% probability cells Leave Random Seed Blank $Q$8 = Output Range OK OK to overwrite These are the instructions on how to get the order, right?

01-04-2006, 08:47 AM	#3
arod23 Major Leagues Join Date: Apr 2004 Location: Philadelphia, PA Posts: 378	You need to be able to run a Random Number Generator based on a Discrete Probability Function. That is one of the function included in Excel's Data Analysis Tool. I don't know if Open Office has that function or if it is an add-on. (Note, the standard random number generator won't help you here).

01-05-2006, 06:31 PM	#6
Jeremy Weimer Hall Of Famer Join Date: Jun 2003 Location: Minneapolis, MN Posts: 3,411	Anyone at all?

01-05-2006, 06:53 PM	#7
arod23 Major Leagues Join Date: Apr 2004 Location: Philadelphia, PA Posts: 378	OpenOffice doesn't appear to have that function preprogrammed but you might be able to create a macro to do it. Here is an example of how to create a random "normal" distribution using the rand() function. http://www.openofficetips.com/blog/a...m_numbers.html The discrete probability is a different formula though.

01-08-2006, 10:14 PM	#10
jf7623 Banned Join Date: Aug 2004 Posts: 713 Infractions: 0/4 (4)	awesome tool