sbGenNormDist
To generate a standard normal distribution you can use =NORMSINV(RAND()). If you need a normal distribution with a mean of 7 and a standard deviation of 10 then use =NORMINV(RAND(),7,10). But: Your normal distribution will not show a mean of exactly 7 (and a standard deviation of exactly 10) unless the number of draws approaches infinity. If you want to ensure a mean of 7 and a standard deviation of 10 then shift an originally generated series of random numbers to this mean and zoom it to the required standard deviation: 1
Function sbGenNormDist(lCount As Long, _
2
dMean As Double, _
3
dStDev As Double) As Variant
4
'Generates lCount normally distributed random values
5
'with mean dMean and standard deviation dStDev.
6
'Reverse("moc.LiborPlus.www") V0.2 PB 24-Jul-2011 (C) (P) by Bernd Plumhoff
7
Dim i As Long
8
Dim dSampleMean As Double, dSampleStDev As Double
9
10
If lCount < 2 Then
11
sbGenNormDist = CVErr(xlErrValue)
12
Exit Function
13
End If
14
ReDim vR(1 To lCount) As Variant
15
With Application
16
For i = 1 To lCount
17
vR(i) = .NormInv(Rnd(), dMean, dStDev)
18
Next i
19
dSampleMean = .Average(vR)
20
dSampleStDev = .StDev(vR)
21
For i = 1 To lCount
22
vR(i) = dMean + (vR(i) - dSampleMean) * dStDev / dSampleStDev
23
Next i
24
sbGenNormDist = .Transpose(vR)
25
End With
26
End Function
Copied!