Please note javascript is required for full website functionality.
MVP

Blog

A to Z of Excel Functions: the LOGNORMDIST Function

22 November 2021

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the LOGNORMDIST function.

 

The LOGNORMDIST function

A Lognormal distribution is a statistical distribution of logarithmic values from an underlying Normal distribution.  A Lognormal distribution may be translated to a Normal distribution and vice versa using the associated logarithmic translations.

A Normal distribution is a symmetrical probability distribution of outcomes that forms a bell curve.  In a Normal distribution, 68% of the results fall within one standard deviation and 95% fall within two standard deviations, viz.

While most people are familiar with a Normal distribution, they may not be as familiar with the related Lognormal distribution.  A Normal distribution may be converted to a Lognormal distribution using logarithms.  It should be noted that Lognormal distributions can only arise from a Normally distributed set of random variables.

There can be a few reasons for using Lognormal distributions in conjunction with Normal distributions.  Most commonly, Lognormal distributions are the result of taking the natural logarithm where the base is equal to e=2.718...  However, the Lognormal distribution may be scaled using a different base, if desired, which affects the shape of the Lognormal distribution.

The Lognormal distribution plots the logarithm of random variables from a Normal distribution curve.  In general, the logarithm is known as the exponent to which a base number must be raised in order to produce the random variable (x) that is found along a Normally distributed curve.  It should be noted that Lognormal distributions are positively skewed with long right tails due to low mean values and high variances in the random variables.

Clear as mud, yes?

In practice, Normal distributions may present a few problems that Lognormal distributions can solve.  In particular, Normal distributions allow for negative random variables whilst Lognormal distributions include all positive variables.

Probably (get it?) the most common application where Lognormal distributions are used in finance is in the analysis of stock prices.  A Lognormal distribution is more suitable for this purpose because asset prices cannot be negative.  An important point to note is that when the continuously compounded returns of a stock follow a Normal distribution, then the stock prices follow a Lognormal distribution.  The Lognormal distribution curve may therefore be used to help better identify the compound return that the stock can expect to achieve over a period of time.

The distribution is occasionally referred to as the Galton distribution or Galton's distribution, after Francis Galton.  In summary, a Lognormal process is “simply” the statistical realisation of the multiplicative product of many independent random variables, each of which is positive and it is therefore the maximum entropy probability distribution for a random variate x for which the mean and variance of LN(x) are specified.  And if you follow all of that, get out of here because no one likes a smartarse. 

The LOGNORMDIST function returns the Lognormal distribution of x, where LN(x) is Normally distributed with parameters mean and standard_deviation.  The LOGNORMDIST  function employs the following syntax to operate:

LOGNORMDIST(x, mean, standard_deviation)

The LOGNORMDIST function has the following arguments:

  • x: this is required and represents the value at which to evaluate the function
  • mean: this is also required and denotes the mean of the natural logarithm of x, LN(x)
  • standard_deviation: again required, this is the standard deviation of LN(x).

It should be noted that:

  • if any argument is nonnumeric, LOGNORMDIST returns the #VALUE! error value
  • if x ≤ 0 or if standard_deviation ≤ 0, LOGNORMDIST returns the #NUM! error value
  • the equation for the lognormal cumulative distribution function is:

This function has been replaced with a new function (LOGNORM.DIST) that may provide improved accuracy and whose names better reflect their usage.  Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel.

Please see my comprehensive example below:

 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.

 

A full page of the function articles can be found here.

 

Newsletter