Selecting at random with weighted probability

[Damage]

I'm trying to figure out the algorithms for a football-simulation game.
If I have a series of numbers which represent the skill levels for
various players, what function or combination of functions will return
a random player where a higher skill level means a higher probability
of being returned by the function?

E.g. player A = 5, player B = 10, player C = 50: I want to return a
random player, such that player B is twice as likely to be returned as
player A, and player C is five times more likely to be returned than
player B, etc.

I think a random number between 1 and A+B+C must be involved, but how
to correlate that random number with the different probabilities? (Some
horribly nested IF functions might work, but it could get pretty ugly
if there are a lot of players and the skill values are variable).
Any ideas, you clever people?

 

[Jason Morin]

If you divide your weights by 5, you get:

1 A
2 B's
10 C's

In E1:E13 I put 1 A, 2 B's, and 10 C's. Then I use:

=INDEX(E1:E13,RANDBETWEEN(1,COUNTA(E1:E13)))

to select a random letter from E1:E13. Make sure you have
the Analysis ToolPak installed (Tools > Add-Ins) before
using RANDBETWEEN.

HTH
Jason
Atlanta, GA

 

[Bernd Plumhoff]

If you like to generate random teams with the same sum of
skill-factors you might consider my function RandSum1() or
if you want to generate teams with stronger/weaker
attackers/defenders you can apply my Redw() function.

See http://www.sulprobil.com/html/random_numbers.html

HTH,
Bernd