Article ID: 828281  View products that this article applies to. On This PageSUMMARYThis article describes the LOGNORMDIST function in Microsoft Office Excel 2003 and in later versions of Excel, illustrates how the function is used, and compares the results of the
function in Excel 2003 and in later versions of Excel with the results of LOGNORMDIST in earlier versions of
Excel. MORE INFORMATIONThe LOGNORMDIST(x, mu, sigma) function returns the
cumulative probability that the observed value of a Lognormal random variable
with mean mu and standard deviation sigma will be less than or equal to x. Syntax
Example of usageBecause LOGNORMDIST and NORMSDIST are closely related, we recommend that you first become familiar with NORMSDIST by reading the following article about NORMSDIST and by examining its sample Excel worksheet.For more information about NORMSDIST, click the following article number to view the article in the Microsoft Knowledge Base: 827369 To
illustrate the relationship between LOGNORMDIST and NORMSDIST, create a blank Excel worksheet, copy the following table,
select cell A1 in your blank Excel worksheet, and paste the entries so that
the table below fills cells A1:G7 in your worksheet.
(http://support.microsoft.com/kb/827369/
)
Excel statistical functions:
NORMSDIST
Collapse this table
In the worksheet example, Column A3:A7 shows 5 values of x and B3:B7 shows LN(x) for each of these 5 values. Values in A3:A7 were established by evaluating the constant for Euler, e, raised to various powers through use of Excel's EXP function. This was done only to illustrate the inverse relationship between EXP and LN. Because LN(EXP(argument)) = argument for any value of argument, the values of LN that appear in column B are just the arguments of EXP in Column A. There is no reason that you cannot add more rows to the spreadsheet, put arbitrary positive numbers in column A in these rows (with no need to call EXP) and fill down B7:G7 to columns B through G of these rows. In the example, mu is set to 3 and sigma to 2. Columns E through G illustrate the following fact: LOGNORMDIST(x,mu,sigma) = NORMSDIST((LN(x)  mu)/sigma) A normal random variable with a given mean and standard deviation
takes on values within two standard deviations of the mean with probability
slightly higher than 0.95. In our example, with mean 3 and standard deviation
2, this range is between 1 and 7. Notice the much broader disparity of values
in the corresponding Lognormal random variable. Two standard deviations below
the mean is 0.37; two standard deviations above the mean is 1096.63. Notice
also that whereas a normal random variable can take on negative values, a
Lognormal random variable is restricted to positive values.Results in earlier versions of ExcelAccuracy of NORMSDIST (and therefore the accuracy of LOGNORMDIST) has been improved in Excel 2003 and in later versions of Excel. In earlier versions of Excel, a single computational procedure was used for all calls to NORMSDIST (either directly or internally in Excel when you make a call to LOGNORMDIST). Results of NORMSDIST were essentially accurate to 7 decimal places. This is more than sufficient for most practical examples.Results in Excel 2003 and in later versions of ExcelThe computational procedure for NORMSDIST in Excel 2003 and in later versions of Excel uses two different procedures, depending on the standardized value, z, that is used by NORMSDIST. If you call LOGNORMDIST(x, mu, sigma), the standardized value, z, used by NORMSDIST is (LN(x) – mu)/sigma. The first computational procedure is for z between 5 and +5; the second is for z values in the extreme left or right tails, below 5 or above +5.Accuracy was improved for all values because, over the range of z values where each was used, these two methods were both superior to the single method that was used in earlier versions of Excel. Typical accuracy is now 14 to 15 decimal places. ConclusionsThere are rare occasions in which you might demand accuracy that is better than 7 decimal places. On such occasions, the versions of LOGNORMDIST and NORMSDIST in Excel 2003 and in later versions of Excel will give superior performance. For all other computations that involve LOGNORMDIST and NORMSDIST, you will not notice a difference between later versions of Excel and earlier versions of Excel.PropertiesArticle ID: 828281  Last Review: September 19, 2011  Revision: 3.0
