This shouldn't be hard for the excel experts.

here's my code:

Function RandNormCorr(mean1, sd1, mean2, sd2, corr)
'returns two random numbers that are based on different distributions
'and has a certain correlation

Dim xymat(1, 2) As Variant 'output array
Dim z1 As Variant
Dim z2 As Variant

z1 = gauss 'gauss is a random number generation function
z2 = gauss

x = z1 * Sqr((1 + corr) / 2) + z2 * Sqr((1 - corr) / 2)
y = z1 * Sqr((1 + corr) / 2) - z2 * Sqr((1 - corr) / 2)

xymat(1, 1) = mean1 + x * sd1
xymat(1, 2) = mean2 + y * sd2

RandNormCorr = xymat


End Function

I want a (1,2) array to show:
mean1 + x * sd1 on the left cell and
mean2 + y * sd2 on the right cell

Of course I highlight a 1x2 range, type the function, the
ctrl+shift+enter

this is what comes out:
0 0
pressing f2 f9 will reveal this
={0,0,0;0,-0.926298991003733,-1.12127338720803}
={0,0,0;0,-1.57926241712754,-0.0663085926997917

--
Message posted from http://www.ExcelForum.com