Generating correlated random uniform numbers

**huntp1** · 06-20-2012, 02:55 AM

Hi there,

I'm looking to generate correlated random unifrom variables between 0 and 1 using the excel random number generator, rand(), and a user defined correlation matrix - without the use of a plugin.

After searching someone directed me to the 'cholesky function' which I've tried to use in the attached file.

Problem is that half the time the last resulting variable is above 1 and they are in increasing order.

Can anyone point me in the right direction please?

**huntp1** · 06-20-2012, 06:43 PM

bump, anyone?

**shg** · 06-20-2012, 07:58 PM

Maybe ...

A2: =RAND()

B2: =RAND()

C2: =(Correl*A2 + SQRT(1-Correl^2)*B2)/(Correl + SQRT(1-Correl^2))

A2 and C2 have the specified correlation

**shg** · 06-20-2012, 08:18 PM

Maybe for a more pleasing distribution ...

Generate a pair of correlated random normal deviates (I can post code) and convert those via =NORMSDIST(A2) and =NORMSDIST(B2)

**huntp1** · 06-20-2012, 08:31 PM

Yes, but then a2 and b2 would be uncorrelated. The 3 number version I gave is really just an example. I'm looking for something more robust, that would provide correlation for around ten different numbers.

I realise there are plenty of risk plugins I could use, but was looking for something excel/vba reliant so I could distribute it without the user having to install anything.