View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
zvkmpw zvkmpw is offline
external usenet poster
 
Posts: 153
Default Statistics / Probability

I am trying to create a model in Excel to spit out a
number based on a computing probabilities associated
with outcomes. An example would be best:

Numbers Probability of occurring
1-10 5%
11-20 10%
21-60 65%
61-85 15%
86-100 5%


Maybe something like this would work.
*
I put a "helper" column in E1:E20 containing
=RANDBETWEEN(1,10)
=RANDBETWEEN(11,20)
=RANDBETWEEN(11,20)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(21,60)
=RANDBETWEEN(61,85)
=RANDBETWEEN(61,85)
=RANDBETWEEN(61,85)
=RANDBETWEEN(86,100)
Notice the repetitions inspired by the "probability of occurring" distribution in the original post. (The column can be hidden to reduce clutter.)
*
Then in A1, I put
=INDEX($E$1:$E$20,INT(20*RAND())+1)
and copied down to make the desired list.