Generate random sequence based on probability

  • Thread starter Thread starter James R
  • Start date Start date
J

James R

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!!
 
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
 
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
 
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
 
Back
Top