Invert the cumulative probability function.

Suppose A1:B5 is the input range to the ATP Random function, then A1:A5 are
the possible values with with B1:B5 their probabilities of occurance
(SUM(B1:B5) should be 1). Instead of B1:B5, use C1:C5 where C5 contains the
formula =SUM(B$1:B5)which you copy/paste over the range
r=rnd
For i = 4 To 1 Step -1
If r Cell(i, 3) Then
rNum = Cell(i + 1, 1)
Exit For
End If
Next c
If i = 0 Then rNum = Cell(1, 1)

Jerry

"festdaddy" wrote:

Thanks all for your responses. Jerry, I'm not sure how I could use Rnd
to make a discrete distribution. Any suggestions would be appreciated.

Jerry W. Lewis wrote:
Use the VBA Rnd() function to roll your own. Unfortunately, VBA cannot call
the worksheet function RAND(), since in Excel 2003 or later it is a much
better random number generator than either Rnd() or the ATP Random function.

Jerry

"festdaddy" wrote:

I'm using the analysis toolpack random function to generate a list of
randoms with a discrete probability
[Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("f30"), 1,
10000, 7, , ActiveSheet.Range("b25", "c54")]

When I try to get the output to go to an array however, it doesn't
work. I get an error saying something to the effect of macro " can't be
found. Does anyone know of a way to get the output of the random
function directly into an array?