How to create a random number following a lognormal distribution in excel?
excel, math, random, vba
Solution
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 ))
Problem
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.