CODEWITHSUNDEEP
excelmathrandomvba
ciso
ciso

Reputation: 3050

How to create a random number following a lognormal distribution in excel?

To create a set of data with a mean of 10 and a standard deviation of 5, as an example, it can be calculated with a normal distribution using this formula:

NORMINV(RAND(), Mean, Stdev)
NORMINV(RAND(), 10, 5)

This results in numbers ranging from roughly -5 to to 25 with a frequency distribution that looks normal with most values centerred around the mean.

How to get a similar set of numbers but have them follow a lognormal distribution, one where there is a higher probability of larger numbers and no probability of going below zero?

How to do this using Excel without add-ons?

Tried:

 LOGINV(RAND(), Mean, Stdev)
 LOGINV(RAND(), 10, 5)

.. but this creates very large numbers (to the power of 20, etc) that look meaningless.

Upvotes: 10

Views: 38682

Answers (2)

Ilgeo
Ilgeo

Reputation: 9

I'm trying to follow the directions above. Please let me know if I've made any mistakes.

Public Function LognormalRV(ByVal SampleMean As Double, ByVal SampleStDev As Double)
    'returns a random variable based on a lognormal distribution
    Dim ScaledMean As Double
    Dim ScaledStDev As Double
    ScaledMean = Log(SampleMean ^ 2 / Sqr(SampleMean ^ 2 + SampleStDev ^ 2))
    ScaledStDev = Sqr(Log((SampleMean ^ 2 + SampleStDev ^ 2) / SampleMean ^ 2))
    LognormalRV = WorksheetFunction.LogNorm_Inv(Rnd(), ScaledMean, ScaledStDev)
End Function

Upvotes: -1

duncan
duncan

Reputation: 456

Lognormal is e^N(m,s). So the answer, using your construct for normal, would be

=EXP( NORMINV(RAND(),Mean,Stdev) )

However that will give you very large values. Next step is to scale the mean and standard deviation. In pseudocode,

scaled mean = ln( m^2 / sqrt( m^2 + s^2 ))
scaled sd = sqrt( ln(( m^2 + s^2 ) / m^2 ))

Upvotes: 12

Related Questions