Generate random sequence based on probability
 

I have a list of events and the probability that each will happen. Example:
Event A 12%
Event B 20%
Event C 2% ...and so on.
My list is about 20 entries long. What I want to do is to have Excel
randomly determine what event will happen in what order, based on the
percentage chance that it will happen. So I want 20 cells in another column
with the results looking something like this:
Event K
Event B ...and so on. And be able to recalculate upon refresh.

Thanks in advance!!

Generate random sequence based on probability
 

Assuming the list is in A1:A20 - In cell C1 type:
=INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20,ROW()),$B $1:$B$20,))
and copy down to C20
Micky


"James R" wrote:

I have a list of events and the probability that each will happen. Example:
Event A 12%
Event B 20%
Event C 2% ...and so on.
My list is about 20 entries long. What I want to do is to have Excel
randomly determine what event will happen in what order, based on the
percentage chance that it will happen. So I want 20 cells in another column
with the results looking something like this:
Event K
Event B ...and so on. And be able to recalculate upon refresh.

Thanks in advance!!

Generate random sequence based on probability
 

This will produce a static list based on the assigned %age, however if you
want to randomise the result then the following will give you this:

in cell C1 =Rand()*B1

and in cell D1 put Mickey's answer, but change the reference from colmn B to
C...

=INDEX($A$1:$A$20,MATCH(LARGE($C$1:$C$20,ROW()),$C $1:$C$20,))

Drag the formulae down and the order will change on every calculation event
randomly, but wheighted by the %age assigned...

--
If this is the answer you hoped for please remember to click the yes button
below...

Kind regards

Rik


"מיכאל (מיקי) אבידן" wrote:

Assuming the list is in A1:A20 - In cell C1 type:
=INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20,ROW()),$B $1:$B$20,))
and copy down to C20
Micky


"James R" wrote:

I have a list of events and the probability that each will happen. Example:
Event A 12%
Event B 20%
Event C 2% ...and so on.
My list is about 20 entries long. What I want to do is to have Excel
randomly determine what event will happen in what order, based on the
percentage chance that it will happen. So I want 20 cells in another column
with the results looking something like this:
Event K
Event B ...and so on. And be able to recalculate upon refresh.

Thanks in advance!!

Generate random sequence based on probability
 

Great answers, both of you. Thanks, Rik and Mickey! However, I have a couple
of concerns:
1. In the last record of the "INDEX" formula, I get a "#NUM" error that I
can' seem to fix.
2. I understand that just because something has, for example, a 35%
probability of happening first that it won't happen first 35% of the time.
However, that event is not even coming close to happening 30 % of the time.
Any thoughts?

Thanks,
James

"Rik_UK" wrote:

This will produce a static list based on the assigned %age, however if you
want to randomise the result then the following will give you this:

in cell C1 =Rand()*B1

and in cell D1 put Mickey's answer, but change the reference from colmn B to
C...

=INDEX($A$1:$A$20,MATCH(LARGE($C$1:$C$20,ROW()),$C $1:$C$20,))

Drag the formulae down and the order will change on every calculation event
randomly, but wheighted by the %age assigned...

--
If this is the answer you hoped for please remember to click the yes button
below...

Kind regards

Rik


"מיכאל (מיקי) אבידן" wrote:

Assuming the list is in A1:A20 - In cell C1 type:
=INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20,ROW()),$B $1:$B$20,))
and copy down to C20
Micky


"James R" wrote:

I have a list of events and the probability that each will happen. Example:
Event A 12%
Event B 20%
Event C 2% ...and so on.
My list is about 20 entries long. What I want to do is to have Excel
randomly determine what event will happen in what order, based on the
percentage chance that it will happen. So I want 20 cells in another column
with the results looking something like this:
Event K
Event B ...and so on. And be able to recalculate upon refresh.

Thanks in advance!!

Generate random sequence based on probability
 

Hello James,

I suggest to use my UDF redw:
http://sulprobil.com/html/distributions.html

Regards,
Bernd

Generate random sequence based on probability
 

Hello James,

If the "likelihood" is not sufficient for you then have a look at my
UDF ExactRandHistogrm, please:
http://sulprobil.com/html/exactrandhistogrm.html

Regards,
Bernd