Option Explicit
Function rsNORMAL(Optional ByVal rsMean, Optional ByVal rsSD, _
Optional rsMin, Optional rsMax)
'Generates random normal variable.
Dim a As Double
With Application
a = Sin(2 * .Pi() * Rnd()) * (-2 * Log(Rnd())) ^ 0.5
If Not IsMissing(rsSD) And Not IsMissing(rsMean) Then
a = a * rsSD + rsMean
End If
If Not IsMissing(rsMin) And Not IsMissing(rsMax) Then
a = .Min(rsMax, .Max(rsMin, a))
End If
End With
rsNORMAL = a
a = vbEmpty
End Function
Function rsLOGNORMAL(Optional ByVal rsMean, Optional ByVal rsSD, _
Optional rsMin, Optional rsMax)
'Generates random lognormal variable.
Dim a As Double
If Not IsMissing(rsSD) And Not IsMissing(rsMean) Then
rsSD = (Log((rsSD / rsMean) ^ 2 + 1)) ^ 0.5
rsMean = Log(rsMean) - rsSD ^ 2 / 2
a = Exp(rsSD * rsNORMAL() + rsMean)
Else
a = Exp(rsNORMAL())
End If
If Not IsMissing(rsMin) And Not IsMissing(rsMax) Then
With Application
a = .Min(rsMax, .Max(rsMin, a))
End With
End If
rsLOGNORMAL = a
a = vbEmpty
End Functio
--
Message posted from
http://www.ExcelForum.com