Need a way to do random drawing with weighted chances

  • Thread starter Thread starter PMD
  • Start date Start date
P

PMD

Hi All: I am trying to do a monthly drawing that will be generated
from a report. In column "A" is the person's name and in column "B" is
a number that will represent the chances for the drawing that the
person in column "A" gets. In other words a person with the number 8
in column "B" will have 8 times the chance to win as the person with
the number 1 in column "B". Is there a formula I can use that will
increase the person's chance of winning based on the number in column
"B"?
Thanks in advance for your help.
 
Need more info about the weighting you want to apply...
Using the examples (Person1=8, Person2=1):
1. What is the original basis of the 8 and the 1?
2. By how much do you want the chances to increase based
on the number in B? Or do you want a random amount of
increase? A random amount with an lower and/or upper
limit?
3. Do you want to keep the original numbers and calculate
a new number in column C to represent the increased
chances?
4. Do you want everyone's chances to increase, or just
some? If just some, how do you choose which ones to
recalculate?

Maybe more questions based on your reply. Please post
back.

HJ
 
PMD wrote...
. . . In column "A" is the person's name and in column "B" is a
number that will represent the chances for the drawing that the
person in column "A" gets. In other words a person with the
number 8 in column "B" will have 8 times the chance to win as
the person with the number 1 in column "B". Is there a formula I
can use that will increase the person's chance of winning based
on the number in column "B"?
Click to expand...

The following in A1:B4.

Bob 1
Carol 2
Ted 3
Alice 4

The following *array* formula in A6.

=LOOKUP(RAND(),
MMULT(--(ROW($B$1:$B$4)>TRANSPOSE(ROW($B$1:$B$4))),$B$1:$B$4)/SUM($B$1:$B$4),
$A$1:$A$4)

Fill A6 down into A7:A1005. Enter the following formula in D1.

=COUNTIF($A$6:$A$1005,A1)

Fill D1 down into D2:D4. Pressing [F9] to recalc the random draw
winners in A6:A1005, D1 should vary around 100, D2 around 200, D3
around 300 and D4 around 400.

The A6 formula would be your random draw winner.
 
Thanks for the response. It's as if they purchased tickets to a
drawing. The more tickets they purchase the more chances they have to
win. The person with an "8" will have eight chances to win, four times
the chances than the person that has a "2". The numbers are relative
to each other, they are not random.
To explain, the number is the number of successful sales that person
had for the month and for each sale they get a chance in the drawing.
They come out in a report with the total number of sales, so I am
trying to figure out a way to do the drawing with the information as
it is given
 
Thanks I will try that as soon as I can.



hgrove said:
PMD wrote...
. . . In column "A" is the person's name and in column "B" is a
number that will represent the chances for the drawing that the
person in column "A" gets. In other words a person with the
number 8 in column "B" will have 8 times the chance to win as
the person with the number 1 in column "B". Is there a formula I
can use that will increase the person's chance of winning based
on the number in column "B"?
Click to expand...

The following in A1:B4.

Bob 1
Carol 2
Ted 3
Alice 4

The following *array* formula in A6.

=LOOKUP(RAND(),
MMULT(--(ROW($B$1:$B$4)>TRANSPOSE(ROW($B$1:$B$4))),$B$1:$B$4)/SUM($B$1:$B$4),
$A$1:$A$4)

Fill A6 down into A7:A1005. Enter the following formula in D1.

=COUNTIF($A$6:$A$1005,A1)

Fill D1 down into D2:D4. Pressing [F9] to recalc the random draw
winners in A6:A1005, D1 should vary around 100, D2 around 200, D3
around 300 and D4 around 400.

The A6 formula would be your random draw winner.
Click to expand...
 
Hi,
I looked at hgrove's reply. I tried it on my copy of
Excel 97, but was unable to get a result. The formula for
cell A6 was returning an error. My software may not have
the same functionality, so I can't say whether that
suggestion might produce the desired result. Give it a
shot and see what you think.

My picture of the problem is: you want to generate the
equivalent of virtual tickets to throw in a virtual hopper
for a virtual random draw, the number of tickets for each
person being equal to the single number you have for that
person's sales.

Logically what you want to end up with is a list of
names with a number of records - 1 for Bob, 2 for Carol, 3
for Ted and 4 for Alice (to use hgrove's example), etc..
Each record, representing one chance, would be tagged with
a random number (=RAND()). The random number list would
be cycled some number of times (F9 to recalculate the
sheet, sort of "shuffling the deck"), and then the Name
and Random Number list would be sorted on the random
number column. Whichever name floats to the top is the
winner.

This sounds good, but you might have trouble convincing
the participants that the result is truly random. Even
the big lotteries use "bouncing balls" plucked from a
hopper. MY suggestion, for what it's worth, is to
generate a real token of some sort (tickets with the
person's name on it, colored buttons, numbered balls with
number blocks assigned to each person - Bob gets #1, Carol
gets #2 and #3, etc.), a token for each chance that person
rates. Shuffle the tokens somehow and have a random,
disinterested person draw the winning token.

I know this is not what you wanted, and the scope of
your problem may be larger than you've revealed. HOWEVER,
if the prize is valuable and you have a lot of people
involved, you really need to make sure that the drawing is
demonstrably fair, weighted properly (number of tokens in
the hopper), and random.

Good luck.
 
Back
Top