Discrete random numbers into an array?

[festdaddy]

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?

 

[Tom Ogilvy]

it won't output directly to an array.

You will need to write your own generator.

 

[Jerry W. Lewis]

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]

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.

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

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?

 

[Jerry W. Lewis]

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

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.

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

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?