Log Normal Distribution

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

Does anyone know how to generate random numbers with lognorma
distributions? I am assuming that I can use the LogInv and LogNormDis
function. But as with the normal distributions, I would assume tha
this would be slow. Does anyone know of a VBA sub that generate
random numbers lognormally
 
EXP(z) where z is a normal random number. In 2003 with the recent patch
for the RAND() function, you could use

=EXP(NORMINV(RAND(),mean,stdev)

but this will frequently give unsatisfactory results in earlier versions.

Jerry
 
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
 
Back
Top