Many thanks in advance for any help on this one.
Here is the scenario: I would like to calculate the
performance probabilities for each player in a raffle.
Each player does not have the same number of tickets.
Each player can win only one prize (once a player has won
a prize, other drawn tickets for that player are void)
All tickets are eventually drawn out. I am interested in
the probability of each player winning 1st, 2nd, 3rd ..
etc prize. This will be a function of the distribution of
tickets.
For 3 players this would be straight forward and could be
done on the worksheet w/o any vba code, for example:
If A has 3 tickets, B has 2 tickets, and C has 1 ticket
then, P(A=1st) = 3/6 = 50%, P(B=1st) = 2/6, etc.
P(A=2nd) = P(B=1st)*P(AC) + P(C=1st)*P(AB)
= (2/6 * 3/4) + (1/6 * 3/5)
P(A=3rd) = 1-P(A=1st)-P(A=2nd)
The simple example above analyzes each permutation or
outcome. A problem arises when there are a larger number
of players involved. Outcomes/Permutations soon run into
billions even for say 12 players there are 12P12 = 479
milion outcomes.I feel that there is likely to be ways of
reducing the problem to a managable size. Has anyone got
any ideas (and coding suggestions)?