Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generate random sequence based on probability
Hello James,
I suggest to use my UDF redw: http://sulprobil.com/html/distributions.html Regards, Bernd |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Probability of random numbers | Excel Discussion (Misc queries) | |||
How do I generate a Sequence Number System in Excel? | Excel Discussion (Misc queries) | |||
generate a random number and use if function to generate new data | Excel Worksheet Functions | |||
GENERATE RANDOM NUMBERS BUT EXCLUDE A NUMBER IN THE SEQUENCE | Excel Discussion (Misc queries) | |||
Selecting at random with weighted probability | Excel Worksheet Functions |