VBA Macro - correlations in random number generation

[jomni]

I'm working a random number generator that follows normal distribution
(given the mean and stadard deviation) and correlations.

I have done the normal distribution requirement through the gaus
formula. The normdist function is also a good shortcut.

I'm now finding ways to make the random numbers (two variables) follo
a certain correlation behavior.

My guess is that I let the macro create random numbers and compute th
correlation. If it does not result into the target correlation, the
it restarts the process. But this may go on forever because I nee
successful 10,000 runs.

Any suggestions

 

[Michael R Middleton]

jomni -

I'm working a random number generator that follows normal distributions

(given the mean and stadard deviation) and correlations.

I have done the normal distribution requirement through the gauss formula.

The normdist function is also a good shortcut.

I'm now finding ways to make the random numbers (two variables) follow a certain correlation behavior.
My guess is that I let the macro create random numbers and compute the

correlation. If it does not result into the target correlation, then it
restarts the process. But this may go on forever because I need successful
10,000 runs.

Any suggestions?

If you don't want to "reinvent the wheel," download risk230e.xla from the
"Decision Modeling Using Excel" page of my university web site
www.usfca.edu/~middleton and use its array-entered RandBiNormal function to
return two correlated normal random variables.

If you want to write your own, go to groups.google.com and search for "excel
normal correlated" (without the quotes).

- Mike Middleton

 

[jomni]

Thanks for the example.

I'm currently reading your manual (the pdf file) But I don't see an
RandBiNormal function there, just RandBinomial