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/2016 provide the following additional functions: LOGNORM.DIST(x, μ, σ, cum) where cum takes the values TRUE or FALSE and LOGNORM.DIST(x, μ, σ, TRUE) = LOGNORMDIST(x, μ, σ, TRUE), LOGNORM.DIST(x, μ, σ, FALSE) = NORM.DIST(LN(x), μ, σ, FALSE)/x, and LOGNORM.INV which is equivalent to LOGINV.

12 Responses to Log-normal Distribution

  1. 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?

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

  4. Yashasvi says:

    Hey can someone tell me what is the use of ‘x’ in generating data following log normal distribution?
    If I want to model for some losses which is random variable ‘x’ then how do I apply the formula?
    The main thing is that I want to generate some data following log normal distribution then what should I do?

    • Charles says:

      Yashasvi,
      To generate random data that follows the log normal distribution with mean m and standard deviation s, use the formula =LOGNORM.INV(RAND(),m,s)
      Charles

  5. Sinsin says:

    Hello,
    I got values with LOGNORM.INV(RAND(),m,s);
    s=SQRT(LN(1+25.6^2/57.3^2)) and m= LN(57.3)-0.5*sigma^2. But i have to use normal values after this calculation. How can i get normal values? Because i think i got z values which means z=(Inx-m)/s???

    • Charles says:

      Sinsin,
      I am sorry, but I don’t understand what you mean by “how can I get the normal values?”.
      You can transform a value that follows a lognormal distribution to a normal distribution by using the function f(x) = EXP(x). Thus if the value from LOGNORM.INV(RAND(),m,s) were say 2, then the normal transformation would yield the value EXP(2) = 7.389.
      Charles

Leave a Reply

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