LOGNORM.DIST function

The LOGNORM.DIST function in Excel calculates the logarithmic normal distribution, which is used to model data that is positively skewed and follows a distribution where the logarithm of the variable is normally distributed. This function is particularly useful for modeling data in fields such as finance, economics, and environmental science, where the data tends to have a skewed distribution (i.e., long tail on the right).

The log-normal distribution is based on the assumption that the natural logarithm of a variable follows a normal distribution.

Syntax:

LOGNORM.DIST(x, mean, standard_dev, cumulative)

Arguments:

• x: Required. The value for which you want to calculate the log-normal distribution. This must be a positive number because the log-normal distribution is defined only for positive values.
• mean: Required. The mean (average) of the natural logarithm of the distribution (i.e., the mean of the underlying normal distribution).
• standard_dev: Required. The standard deviation of the natural logarithm of the distribution (i.e., the standard deviation of the underlying normal distribution).
• cumulative: Required. A logical value that specifies the type of distribution to return:
  • TRUE: Returns the cumulative distribution function (CDF), which gives the probability that the random variable is less than or equal to $x$.
  • FALSE: Returns the probability density function (PDF), which gives the likelihood that the random variable equals $x$.

Output:

• When cumulative is set to TRUE, LOGNORM.DIST returns the cumulative probability up to the value of $x$, i.e., the area under the curve from $-\infty$ to $x$.
• When cumulative is set to FALSE, it returns the probability density at $x$, i.e., the height of the distribution at $x$.

How It Works:

The LOGNORM.DIST function models a log-normal distribution based on the normal distribution of the natural logarithms of the values. If $Y$ follows a normal distribution, then $X = e^Y$ (i.e., the exponential of $Y$) follows a log-normal distribution.

The function uses the following formula for the cumulative distribution:

$$F(x) = \frac{1}{2} \left[ 1 + \text{erf}\left( \frac{\ln(x) – \mu}{\sigma \sqrt{2}} \right) \right]$$

Where:

• $\mu$ is the mean of the underlying normal distribution.
• $\sigma$ is the standard deviation of the underlying normal distribution.
• $\text{erf}$ is the error function used in statistics.

Example:

1. Example 1: Cumulative Probability Suppose you want to find the cumulative probability that a random variable with a log-normal distribution (with a mean of 0.5 and a standard deviation of 0.2) is less than or equal to 1. You can use:

   =LOGNORM.DIST(1, 0.5, 0.2, TRUE)
   

   This will return the cumulative probability that the value of the log-normally distributed variable is less than or equal to 1.

2. Example 2: Probability Density If you want to find the probability density at $x = 1$ for the same distribution, use:

   =LOGNORM.DIST(1, 0.5, 0.2, FALSE)
   

   This will return the height of the distribution curve at $x = 1$, which represents the likelihood of the random variable being exactly 1.

Key Points:

• The LOGNORM.DIST function assumes that the logarithm of the data is normally distributed. Thus, it is useful for modeling data that cannot take negative values and is positively skewed.
• The cumulative distribution (when cumulative is TRUE) gives you the probability that the random variable is less than or equal to $x$.
• The probability density (when cumulative is FALSE) gives you the probability of exactly $x$ occurring at the given value.
• The function is commonly used in fields where data tends to grow exponentially, such as stock prices, income distribution, and population growth models.

Example Interpretation:

• Cumulative Distribution: If the result of the function is 0.8, it means there is an 80% probability that the value of the random variable is less than or equal to $x$.
• Probability Density: If the result is 0.5, it means the probability of exactly $x$ occurring at the given point on the distribution curve is 0.5.

Use Cases:

• Finance: LOGNORM.DIST is frequently used in finance for modeling stock prices, options pricing, and financial returns, as they tend to follow a log-normal distribution.
• Economics: It can be used to model income distribution, where the income of individuals follows a log-normal distribution.
• Environmental Science: The distribution of certain environmental factors, like pollutant concentrations, may follow a log-normal distribution.
• Health and Medicine: Some biological measurements, like the size of certain cells or bacteria, are modeled using log-normal distributions.

Notes:

• The function requires that $x$ be positive, as the log-normal distribution is only defined for positive values.
• The mean and standard deviation used in the function refer to the normal distribution of the natural logarithm of the data, not the log-normal distribution itself.
• The LOGNORM.DIST function is useful for estimating probabilities or density for log-normally distributed data, and it complements other statistical functions such as NORM.DIST, which deals with normal distributions.