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