Randomly selecting items from a list given their statistical likelihood

[MACRE0]

I have one solution, but it's rather cumbersome. I could create a lis
10,000 long with each unique item maintaining its relative percentag
(to 4 decimal places) then putting =RAND() and selecting the highes
number for each selection (60 in one of my cases) then run thi
several hundred more times for an adequate sample size on which t
_begin_ my analysis.

Initially I figured I could simply multiply percentage of occurrence b
a rand() number and take the maximum there 60 times repeatedly. Howeve
it quickly became evident that I was dually weighted by the percentag
(i.e. the ratio of the taking the max between say one item at 6
*RAND() vs. another at 2%*RAND() is more like 30% : 0.2% ) A possibl
solution I figured might be taking the Max of the Square Roots of the
Outcome by 60, but I'm not certain that isn't an arbitrary way o
augmenting the disproportions. I haven't tried it yet so I don't kno
for sure what would happen.

I require a more compact way of creating possible lists of 60 items o
which to perform confidence testing. Any input, be it conceptual o
for practical application, would be welcomed.

Thanks,
MACR

 

[Michael R Middleton]

MACRE -

Although you have described a possible solution, I could not tell what the
problem is that you are trying to solve.

If you have a list of several hundred numerical values (population) and you
want a random sample of sixty (without replacement), you could use the
array-entered RANDSAMPLE function that is part of the RiskSim add-in, file
risk230e.xla, available for download from the "Decision Modeling Using
Excel" page of my university web site www.usfca.edu/~middleton.

Instead, if you have a discrete probability distribution, i.e., a list of
numerical values in one column with associated probabilities in an adjacent
column, you could use the RANDDISCRETE function, part of the same add-in.

Or, you could use industrial-strength add-ins like Crystal Ball
(www.crystalball.com) or @Risk (www.palisade.com).

- Mike

www.mikemiddleton.com

++++++++++++++++++++++++++++++