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