Random chooser

[drgkt]

In a, say, 30 row column where there are 25 Georges and 5 Helens, a random cell pick will most likely be George, Helen not excluded.

I wonder if the the random choice in the the example above can be done in a 2 column, 2 row table like:

Name OccurenceNumber
George 25
Helen 5


The formula should be able to exclude names stored in range x, no exclusions if x range is empty.

[shg]

So you want to generate a random name with George having 25 chances of 27?

[drgkt]

You mean of 30?

Anyway, think of it as a draw out of a pool where each person has entered different amount of times.
Here is another example:

ID OCCURRENCE
1 15
2 189
3 169
4 32
5 191
6 188
7 28
8 187
9 92
10 173

[estige]

Hi, if I understand you correctly, one way is to lookup a random number from the cumulative sums of occurrences.

[estige]

One error in attachment in my previous post:
F1 should be =RANDBETWEEN(1,SUM(B2:B11))

(And not =RANDBETWEEN(1,C11)

[drgkt]

Hi
To make it even more clear in the example provided, a random raffle must be drawn out of a pool where there are 15 balls with number 1, 189 balls with number 2, and so on, a total number of balls 1264 (in the example).

Obviously, number 2 with 189 entries has more chances to be selected than number 1 with only 15 entries, BUT number 1 may still be the random winner.

I could put all 1264 entries in one column where 1-15 is 1, 16-204 is 2 and so on, and do a random there, but since the entries are sequential and NOT MIXED, I do not think it will be a FAIR random.

The point of this is to be fair, otherwise we might as well do a random on A1:A10.

@estige, Thanks for your input. I am not sure, your logic follows what I described above. You care to explain?


P.S. Let's not forget to exclude entries put elsewhere in a column, so we can have 2nd, 3rd winner and so on.

[estige]

Hi
@estige, Thanks for your input. I am not sure, your logic follows what I described above. You care to explain?

Certainly: The draw is a random number between 1 and 1264.
ID1=number 1-15, ID2=16-204,...,ID10=1092-1264. This means that ID1's chance is 15/1264, ID2 189/1264,..., ID10=173/1264. This will give the same result as if you "put all 1264 entries in one column where 1-15 is 1, 16-204 is 2 and so on...", but I see no reason why the sequence should influence a ID's chance of getting drawn using randbetween.

[drgkt]

...but I see no reason why the sequence should influence a ID's chance of getting drawn using randbetween.

You think a random in a column grouped like that would be the same as in a column where ID's would be mixed up?

I am asking because it is a common practice to "shuffle" before drawing!

[shg]

See the second tab of the workbook at https://app.box.com/s/5a06b6he6sc9ce89bbfl

[drgkt]

See the second tab of the workbook at https://app.box.com/s/5a06b6he6sc9ce89bbfl

Thanks shg! Hey why reinvent the wheel?

However, I think MODIFIED TAB 1, suits my quest, since each participant (regardless his number of chances) can win once.
In our example, if 1 wins, ALL 15 balls are removed, before the next draw with the remaining participants.

[shg]

You're welcome.

[drgkt]

You're welcome.

Wait, I spoke too soon. Doesn't column AC in tab 1 show us the win order of all players? We can choose the top 3, 5 or whatever we want, right?

[shg]

Yes and yes.

[drgkt]

I am getting N/A after adding participants, does it matter?

RemainingChances Draw

8410 2226
8209 656
8037 4737
7839 6385
7651 4644
7462 4661
#N/A #N/A
#N/A #N/A
#N/A #N/A


NEVER MIND

[estige]

Yes, as far as I know all numbers between 1 and 1264 will have exactly the same chance with randbetween(1,1264). I can't think of a single argument why changing which ID each number represent will influence on that.

[drgkt]

Ok then, Thanks.