weighted random numbers

[Jorge]

Would appreciated any help on this. I'm looking to create a
spreadsheet that can generate weighted random numbers much like
professional sports leagues do with their draft lotteries. I play in
a league with 20 teams and we have a draft every year. I would like
to generate a lottery where the teams that finished near the bottom
would have a greater ( say i.e 25%) chance of selecting the 1st pick
than the teams that finished near the top. Any input would be
appreciated. Thx

Jorge

 

[Harlan Grove]

Would appreciated any help on this. I'm looking to create a
spreadsheet that can generate weighted random numbers much like
professional sports leagues do with their draft lotteries. I play
in a league with 20 teams and we have a draft every year. I would
like to generate a lottery where the teams that finished near the
bottom would have a greater ( say i.e 25%) chance of selecting
the 1st pick than the teams that finished near the top. Any input
would be appreciated. Thx

If you have team standings in A2:A21 (first to last place), team names
in B2:B21, then enter, say, 0.75 in C2:C5 (top 4 teams have 75% of
average odds), 0.90 in C6:C9 (next 4 have 90% of average odds), 1 in
C10:C13 (middle 4 teams have average odds), 1.1 in C14:C17 (next 4
have 110% of average odds), and 1.25 in C18:C21 (last 4 teams have
125% of average odds). Enter =SUM(C2:C21) in C23. Enter 0 in D2,
=D2+C2/C$23 in D3, fill D3 down into D421. Then use the formula

=LOOKUP(RAND(),D$2$21,B$2:B$21)

to give the team getting the top draft choice. Change the weights to
any monotonically increasing positive values in C2:C21 to change the
odds of getting the top draft choice.

 

[Jorge]

Thanks for all your help. Really appreciate it.

Jorge

 

[Bernd]

Hi Jorge,

I suggest to use my UDF REDW:
http://www.sulprobil.com/html/redw.html

Regards,
Bernd