Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Log Normal Distribution
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Log Normal Distribution
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 ExcelMonkey < wrote: Does anyone know how to generate random numbers with lognormal distributions? I am assuming that I can use the LogInv and LogNormDist function. But as with the normal distributions, I would assume that this would be slow. Does anyone know of a VBA sub that generates random numbers lognormally. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Log Normal Distribution
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Log Normal Distribution
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
normal distribution chart | Charts and Charting in Excel | |||
Normal distribution | Excel Worksheet Functions | |||
Normal Distribution line | Excel Discussion (Misc queries) | |||
NORMAL DISTRIBUTION | Excel Worksheet Functions | |||
Normal Distribution? | Excel Discussion (Misc queries) |