J
jomni
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
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