|
The Excel LOGNORM.DIST Function
Log-Normal Distribution
A variable, x is Log-Normally Distributed
if its natural logarithm is normally distributed.
Or, in terms of Excel functions:
where μ is the mean of ln(x) and σ is the standard deviation of ln(x).
Basic Description
The Excel LOGNORM.DIST function calculates the Log-Normal Probability Density Function or the Cumulative
Log-Normal Distribution Function for a supplied value of x.
The Lognorm.Dist function is new in Excel 2010 and so is not available in earlier versions of Excel.
However, the function is simply a new version of the
Lognormdist function
that is available in earlier versions of Excel.
The format of the Lognorm.Dist function is :
LOGNORM.DIST( x, mean, standard_dev, cumulative )
Where the function arguments are:
| x |
- |
The value at which you want to evaluate the log-normal distribution function
|
| mean |
- |
The arithmetic mean of ln(x)
|
| standard_dev |
- |
The standard deviation of ln(x)
|
| cumulative |
- |
A logical argument which denotes the type of distribution to be used:
| TRUE | = | Cumulative Normal Distribution Function |
| FALSE | = | Normal Probability Density Function |
|
Lognorm.Dist Function Example
Example 1 - Normal Probability Density Function
The chart on the right shows the the Log-Normal Probability Density Function for a variable, x.
The mean of ln(x) is 10 and the Standard Deviation of ln(x) is 5.
If you want to calculate the value of this function at x = 0.5, this can be done using the
Excel Lognorm.Dist function, as follows:
=LOGNORM.DIST( 0.5, 10, 5, FALSE )
This gives the result 0.016210482.
|
Log-Normal Probability Density Function with mean ln(x) = 10 & std. dev. ln(x) = 5
|
Example 2 - Cumulative Log-Normal Distribution Function
The chart on the right shows the the Log-Normal Cumulative Distribution Function for a variable x.
Again, the mean of ln(x) is 10 and the Standard Deviation of ln(x) is 5.
If you want to calculate the value of this function at x = 12, this can be done using the
Excel Lognorm.Dist function, as follows:
=LOGNORM.DIST( 12, 10, 5, TRUE )
This gives the result 0.066417115.
|
Log-Normal Cumulative Distribution with mean ln(x) = 10 & std. dev. ln(x) = 5
|
Further information and examples of the Excel Lognorm.Dist function can be found on the
Microsoft Office website.
Trouble Shooting
If you get an error from the Excel Lognormdist function this is likely to be one of the following:
Common Errors
| #NUM! |
- |
Occurs if the supplied x ≤ 0 or the supplied standard_dev ≤ 0
|
| #VALUE! |
- |
Occurs if any of the supplied arguments are non-numeric
|
|
