Log-normal Distribution

Definition 1: A random variable x is log-normally distributed provided the natural log of x, ln x, is normally distributed. See Exponentials and Logs and Built-in Excel Functions for a description of the natural log.

Observation: As discussed in Transformations, sometimes it is useful to use a transformation of the population being studied. In particular, since the normal distribution has very desirable properties, transforming a random variable into a variable that is normally distributed by taking the natural log can be useful.

Note that the log-normal distribution is not symmetric, but is skewed to the right. If you have data that is skewed to the right that fits the log-normal distribution, you may be able to access various tests described elsewhere in this website that require data to be normally distributed.

Excel Functions: Excel provides the following two functions:

LOGNORMDIST(x, μ, σ) = NORMDIST(LN(x), μ, σ, TRUE)

LOGINV(p, μ, σ) is the inverse of LOGNORMDIST(x, μ, σ); i.e. LOGINV(p, μ, σ) = the value x such that LOGNORMDIST(x, μ, σ) = p

Excel 2010/2013 provide the following additional functions: LOGNORM.DIST(x, μ, σ, cum) where cum takes the values TRUE or FALSE and LOGNORM.DIST(x, μ, σ, cum) = NORM.DIST(LN(x), 0, 1, cum), and LOGNORM.INV which is equivalent to LOGINV.

8 Responses to Log-normal Distribution

  1. Ted says:

    Suggestion to Brian. If you use LOGINV(RAND(),3.9573,0.4266) you will get the distribution you want. As a first approximation, you can use LN(mean) and LN(stdev) in place of mean and stdev, but it is not quite correct. The sigma for the lognormal distribution is SQRT(LN(1+25.6^2/57.3^2)) and the mu is LN(57.3)-0.5*sigma^2. This is how the numbers above were calculated.

  2. Brian says:

    Hello Charles, I’ve been researching this question on the web for several hours, but I can’t find a good answer. Basically, what I want to do is use Excel to generate 300 numbers with a right-skewed distribution that have a mean of 57.3 and standard deviation of 25.6. Ideally, there would be a parameter that controls the amount of skewness. Is there anything you could suggest to achieve this?

    • Charles says:

      Brian,
      You can use the formula LOGNORM.INV(RAND(),57.3,25.6) 300 times. Changing the value of the standard deviation will change the skewness.
      Charles

      • Brian says:

        Charles,

        Thank you so much for the reply. I tried the formula as given, but most numbers generated were very large, such as 3.63754E+22. Not sure what I’m doing wrong.

        Brian

        • Charles says:

          Brian,
          You aren’t doing anything wrong. Based on the values for the mean and standard deviation that you chose, the values for the lognormal distribution are large. If this doesn’t serve your purposes you will need to choose a different distribution.
          Charles

          • Brian says:

            Thanks again Charles. I will look for a different method as the Lognormal distribution doesn’t seem appropriate for this situation.

            Thank you for the excellent website.

            Brian

  3. John says:

    What approach do you use to transform data which may have zero as a value. e.g. Just ignore them, or add a constant to force all value positive or what?

Leave a Reply

Your email address will not be published. Required fields are marked *