STAT / EXCEL Pro? Please come in...

[GTaudiophile]

Let's say I have 4 of the same prize and I need to award these to 5 potential candidates. Meaning, there will be 4 winners and 1 loser. Sadly. There is no way I can award the 4 prizes based on merit or anything. And I want each of the 5 people to have an EQUAL chance of winning a prize until all 4 are exhausted.

This is what I want to do in Excel...

Assign each candidate a random ID number:

Person A, =RANDBETWEEN(0,9)
Person B, =RANDBETWEEN(10,19)
Person C, =RANDBETWEEN(20,29)
Person D, =RANDBETWEEN(30,39)
Person E, =RANDBETWEEN(40,49)

And then have a PICK number: =RANDBETWEEN(0,49)

When the PICK # = Person ID # then that Person wins a prize. I then take that winner out of the scenario and then re-run the pick process until all 4 prizes have been distributed. Of course each time I run this, the random ID numbers and pick numbers change.

Does this logic seem sound for what I want to do?

Should the PICK # remain between 0 and 49 until all 4 prizes have been distributed or should that change as winners are removed from the scenario?

So the second iteration would look like...

Person A, =RANDBETWEEN(0,9)
Person B, =RANDBETWEEN(10,19)

Person D, =RANDBETWEEN(20,29)
Person E, =RANDBETWEEN(30,39)

And then have a PICK number: =RANDBETWEEN(0,39)

 

[OverVolt]

I just made a hat with 5 names in it by the time I read that.

 

[GTaudiophile]

I just made a hat with 5 names in it by the time I read that.

Yes but every time a name is removed the chances improve for those remaining in the hat, correct?

 

[njmodi]

Yes but every time a name is removed the chances improve for those remaining in the hat, correct?

Just put the name back in the hat after you pick it (that person is now considered a winner)... keep picking out of the hat until you end up with 4 different names...

 

[z1ggy]

Yes but every time a name is removed the chances improve for those remaining in the hat, correct?

They improve overall, but not relative to each other, which is what is the important part is.

 

[GTaudiophile]

Well, while the hat is easier...is what I describe in my OP flawed by comparison?

 

[Ken g6]

Let's say I have 4 of the same prize and I need to award these to 5 potential candidates. Meaning, there will be 4 winners and 1 loser.

So you're not willing to accept a scenario where there are, say, 3 winners, one with 2 prizes, and 2 losers?

Yes but every time a name is removed the chances improve for those remaining in the hat, correct?

No, the chances worsen for those remaining in the hat. 4/5 -> 3/4 -> 2/3 -> 1/2. But they are equal among those still in the hat. I think worsening chances is unavoidable, given the at-most-one-prize-per-person restriction, unless you do something like pick the loser first.

 

[dullard]

unless you do something like pick the loser first.

Seems like randomly picking the one loser is the easiest route in this case.

 

[njmodi]

^ That's an interesting way to think about it... if all the prizes are the same, why not just pick the loser... it's one pick and everyone has the same chance at losing.

 

[GTaudiophile]

^ That's an interesting way to think about it... if all the prizes are the same, why not just pick the loser... it's one pick and everyone has the same chance at losing.

Heh. Yeah. That is one way to look at it.

But let's say the next situation has 20 candidates and 10 prizes?

 

[Belegost]

You're overly complicating the situation by trying to randomly assign IDs, and even more by using far more ID values than you need.

First, since you're going to roll for winners until you hit an ID that targets a person, have 50 numbers for 5 winners is unnecessary, you'll just end up having to roll a lot of extra times. You would do just as well assigning 0-4 or 1-5.

Second, random ID assignment, there is a common misconception that this would increase the randomness of the results. If we label the random variable for a person being assigned to an ID as I, and the random variable for an ID being picked as a winner as W, then we realize the probability of winning is p(I=i, W=i) the joint probability that both the random assigned ID is i, and the winning roll is i.

We picked these independently, so we can break that up as p(I=i)*p(W=i), and there is where the misconception happens. For a specific i value, the probability is (1/5)*(1/5) = 1/25 which on the surface seems more random than if we had just fixed the ID value for a person and rolled W, which was 1/5.

But this misses the fact that i can take 5 values, so we need to sum over all possible i that could be a winner: p(I=1)p(W=1) + p(I=2)p(W=2) + ... + p(I=5)p(W=5) = 5*(1/25) = 1/5.

1/5 was the probability we would have if we had just assigned people IDs alphabetically. This is because we have effectively marginalized the joint probability over one of the two independent variables, so we removed the effect of setting the IDs at random at all - or alternatively we have removed the effect of rolling a winning ID, we may as well have just said that 1-4 are winners and whoever ends up with ID 5 loses, the results would be statistically the same.

So my suggestion: set the IDs in some fixed order, alphabetical, whatever. Then call RANDBETWEEN(1,5) and the number that comes up is a winner, label the remaining group 1-4, call RANDBETWEEN(1,4), and so on, until all winners are selected.

Also as pointed out, if the number of losers in the group is smaller than the number of winners, the rolling for losers is equivalent, and you could settle this particular case with one labeling of everyone on 1-5, and one call to RANDBETWEEN(1,5).

 

[stinkycheese]

Step 1. https://www.random.org/faq/#Q3.1

 

[GTaudiophile]

Thanks, Belegost! Excellent. Exactly what I was looking for.

But as I wrote, other scenarios won't be as easy as 4 out of 5.

In one, I have 10 candidates and 4 prizes; in another I have 18 candidates and 9 prizes.

 

[Belegost]

Yea, excel makes this more difficult because it's static tabular based. My suggestion would be to put the names in a column, then in a column to the left, just add =RAND() next to each person. Then sort the list based on the RAND value - the sort process will regenerate new random values and reorder the names randomly.

Then just pick the top N names as winners.

 

[GTaudiophile]

Except in my version it's =ZUFALLSZAHL()

 

[GTaudiophile]

Yea, excel makes this more difficult because it's static tabular based. My suggestion would be to put the names in a column, then in a column to the left, just add =RAND() next to each person. Then sort the list based on the RAND value - the sort process will regenerate new random values and reorder the names randomly.

Then just pick the top N names as winners.

I will do that. Seems simple and clean. Thanks!