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

Question

If I want 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?

I would like to do this using Excel without addons.

I tried:

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

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

Answer 1

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 ))