LOGNORMDIST(x, mu, sigma)
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:
|x||LN(x)||mu||sigma||(LN(x) - mu)/sigma||LOGNORMDIST(x,mu,sigma)||NORMSDIST((LN(x) - mu)/sigma)|
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:
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.
Article ID: 828281 - Last Review: Sep 19, 2011 - Revision: 1