Generating correlated normal random numbers - Part 01

This function generates five columns containing numbers (the headers are not generated by the function):

1. Order number (i)
2.  Uniform random sequence (Z1)
3. Uniform random sequence (Z2)
4. First combination of Z1 and Z2  >>> Z3
5. Second combination of Z2 and Z1 >>> Z4

The pairs (Z1,Z3) and (Z2,Z4) simulate a sample extracted from populations in which the pairs have a chosen value for the correlation. As it happens in sampling you usually get a value for the sampling correlation that differs from the population.

The values returned can be unstandardized specifying for each case the mean and standard deviation.

If you are not satisfied with the outcome, recalculate the spreadsheet. As the random values are generated by VBA code they are not volatile as it happens when you input the Excel built-in RAND function in a spreadsheet cell.

See more about volatility in the follow link in a post entitled Handle Volatile Functions like they are dynamite:

http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

  

The inputs are:

1. Number of lines in the sequences (k)
2. Value of population correlation  

Source:
https://www.blogger.com/blogger.g?blogID=3230308801600609435#editor/target=post;postID=4988601992196925693;onPublishedMenu=posts;onClosedMenu=posts;postNum=1;src=postname

In a future message the remaining models in the source will be presented.

B6:E9	{=xlCorrMtx(B12:E41)}
A12:E41	{=xlRndNormal(C3,E3)}
H6:K9	{=xlCorrMtx(H12:K41)}
H12	=H$3+B12*H$4

Function xlRndNormal(k As Integer, corr)     With WorksheetFunction           Dim i As Integer         Dim rslt()         ReDim rslt(1 To k, 1 To 5)           For i = 1 To k             rslt(i, 1) = i         Next i           For i = 1 To k             rslt(i, 2) = .Norm_S_Inv(Rnd)         Next i           For i = 1 To k             rslt(i, 3) = .Norm_S_Inv(Rnd)         Next i           For i = 1 To k             rslt(i, 4) = corr * rslt(i, 2) + Sqr(1 - corr ^ 2) * rslt(i, 3)         Next i         For i = 1 To k             rslt(i, 5) = corr * rslt(i, 3) + Sqr(1 - corr ^ 2) * rslt(i, 2)         Next i         xlRndNormal = rslt           End With End Function

The code for the function xlCorrMtx is in this blog at

https://www.blogger.com/blogger.g?blogID=3230308801600609435#editor/target=post;postID=4988601992196925693;onPublishedMenu=posts;onClosedMenu=posts;postNum=1;src=postname