I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!


Iman Conover WS

The Iman Conover approach is a good method to generate correlated numbers.

The original paper by Iman and Conover you can find here.

Stephen J. Mildenhall published a very good paper on this approach here.

I created an Excel © file following the example given in Mildenhall’s paper:

The input matrix X:


The target correlation S:


The Cholesky decomposition C of S:


The intermediate matrix M (constant values to to equal Mildenhall’s data:


You can create similar data automatically with array formula in A1:A20:


and with the array formula =randomshuffle($A$1:$A$20) in cells B1:B20 (copy to columns C and D respectively).

Now you get the covariance matrix E:

And its Cholesky decomposition F:


The intermediate matrix T:

You can check the generated correlations:


Calculate the ranks of numbers in the columns of T:


The random shuffle code:

Finally you get your result:


Function RandomShuffle(vtemp As Variant) As Variant
Dim j As Long, k As Long, n As Long
Dim temp As Double, u As Double
With Application.WorksheetFunction
vtemp = .Transpose(.Transpose(vtemp))
n = UBound(vtemp, 1)
j = n
Do While j > 0
    u = Rnd()
    k = Int(j * u + 1)
    temp = vtemp(j, 1)
    vtemp(j, 1) = vtemp(k, 1)
    vtemp(k, 1) = temp
    j = j - 1
RandomShuffle = vtemp
End With
End Function

If you like to use a VBA solution for the Iman Conover approach, look here, please.

If you are interested in downloading a 55 KB Excel 2010 sample file go to my Download page, please.

Sulprobil   Get it done   Contact   Disclaimer   Impressum   Download