A variable, x is Log-Normally Distributed if its natural logarithm is normally distributed.
Or, in terms of Excel functions:In Excel 2010, the LOGNORMDIST function has been replaced by the LOGNORM.DIST function.
The new Lognorm.Dist function in Excel 2010 has been improved, in that it can calculate the both the probability density function and the cumulative distribution function, whereas the Lognormdist function only calculates the cumulative distribution function.
Although it has been replaced, the Lognormdist function is still available in Excel 2010 (stored in the list of compatibility functions), to allow compatibility with earlier versions of Excel.
The Excel LOGNORMDIST function calculates the Cumulative Log-Normal Distribution Function at a supplied value of x.
The format of the function is :
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) |
The above chart on the right shows the the Log-Normal Cumulative Distribution 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 = 12, this can be done using the Excel Lognormdist function, as follows:
This gives the result 0.066417115.
Further information and examples of the Excel Lognormdist function can be found on the Microsoft Office website.
If you get an error from the Excel Lognormdist function this is likely to be one of the following:
#NUM! | - | Occurs if the supplied x ≤ 0 or the supplied standard_dev ≤ 0 |
#VALUE! | - | Occurs if any of the supplied arguments are non-numeric |