Option Explicit
Const BigPos As Single = 3.402823E+38
Const BigNeg As Single = -BigPos
Const SmlPos As Single = 1.401298E-45
Function NLT1(Mean As Single, SD As Single, Optional bVolatile As Boolean = False) As Double
If bVolatile Then Application.Volatile
Do
NLT1 = RandLogNormMV(Mean, SD * SD)(0)
Loop While NLT1 < 1#
End Function
Function RandLogNormMV(m As Single, _
v As Single, _
Optional bVolatile As Boolean = False) As Variant
' shg 2009-1010
' Returns a pair of Log-Normal random variates with mean m and variance v
' [URL]http://en.wikipedia.org/wiki/Log-normal_distribution[/URL]
' u (Real) and s (> 0) are the mean and standard deviation
' of the variable's natural log
' m = exp(u + s^2/2)
' v = (exp(s^2)- 1)* exp(2*u + s^2)
' Given m & v,
' u = Log(m)- Log(1 + v / m^2)/ 2
' s = Sqr(log(1 + v / m^2))
Dim u As Single
Dim s As Single
If bVolatile Then Application.Volatile
u = Log(m) - Log(1 + v / m ^ 2) / 2
s = Sqr(Log(1 + v / m ^ 2))
RandLogNormMV = RandLogNorm(u, s)
End Function
Function RandLogNorm(u As Single, _
s As Single, _
Optional bVolatile As Boolean = False) As Variant
' shg 2008-1230
' Returns a pair of Log-Normal random variates
' [URL]http://en.wikipedia.org/wiki/Log-normal_distribution[/URL]
' u (Real) and s (> 0) are the mean and standard deviation
' of the variable's natural log
' m = exp(u + s^2/2)
' v = (exp(s^2)- 1)* exp(2*u + s^2)
' Given the mean and deviation of the distribution,
' u = Log(m)- Log(1 + v / m^2)
' s = Sqr(log(1 + v / m^2))
Dim afRN() As Single ' pair of random normal variates
If bVolatile Then Application.Volatile
afRN = RandNorm()
RandLogNorm = Array(Exp(u + s * afRN(0)), _
Exp(u + s * afRN(1)))
End Function
Function RandNorm(Optional Mean As Single = 0!, _
Optional Dev As Single = 1!, _
Optional fCorrel As Single = 0!, _
Optional fMin As Single = BigNeg, _
Optional fMax As Single = BigPos, _
Optional bVolatile As Boolean = False) As Single()
' shg 1999-1103
' rev 2008-1205 to add min and max
' Returns a pair of random deviates (Singles) with the specified
' mean, deviation, and correlation, as an alternative to
' =NORMINV(RAND(), Mean, Dev)
' Box-Muller Polar Method
' Donald Knuth, The Art of Computer Programming,
' Vol 2, Seminumerical Algorithms, p. 117
Dim z(0 To 1) As Single
Dim u As Single
Dim v As Single
Dim s As Single
If bVolatile Then Application.Volatile
If fMax < fMin Then fMax = fMin
Do
Do
u = 2! * Rnd - 1!
v = 2! * Rnd - 1!
s = u ^ 2 + v ^ 2
Loop Until s < 1!
s = Sqr(-2 * Log(s) / s)
z(0) = Dev * u * s + Mean
z(1) = Dev * v * s + Mean
Loop Until WorksheetFunction.Max(z) >= fMin And _
WorksheetFunction.Min(z) <= fMax
If fCorrel <> 0! Then z(1) = fCorrel * z(0) + Sqr(1! - fCorrel ^ 2) * z(1)
RandNorm = z
End Function
