Excel Statistical Functions: LOGNORMDIST (828281)

MORE INFORMATION

The 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

Note x, mu and sigma are numeric values. Sigma and x must be greater than 0, but there is no similar requirement on mu.

Example Usage

Because LOGNORMDIST and NORMSDIST are closely related, Microsoft recommends that you first become familiar with NORMSDIST through the article on NORMSDIST and its example Excel worksheet.

For additional information about NORMSDIST, click the following article number to view the article in the Microsoft Knowledge Base:

The worksheet that is discussed below is designed to illustrate the relationship between LOGNORMDIST and NORMSDIST. To see the relationship, create a blank Excel worksheet, copy the following table, and then select cell A1 in your blank Excel worksheet. On the Edit menu, click Paste so that the entries in the table below fill cells A1:G7 in your worksheet.

x	LN(x)	mu	sigma	(LN(x) - mu)/sigma	LOGNORMDIST(x,mu,sigma)	NORMSDIST((LN(x) - mu)/sigma)
=EXP(-1)	=LN(A3)	3	2	=(B3-C3)/D3	=LOGNORMDIST(A3,C3,D3)	=NORMSDIST(E3)
=EXP(1)	=LN(A4)	3	2	=(B4-C4)/D4	=LOGNORMDIST(A4,C4,D4)	=NORMSDIST(E4)
=EXP(3)	=LN(A5)	3	2	=(B5-C5)/D5	=LOGNORMDIST(A5,C5,D5)	=NORMSDIST(E5)
=EXP(5)	=LN(A6)	3	2	=(B6-C6)/D6	=LOGNORMDIST(A6,C6,D6)	=NORMSDIST(E6)
=EXP(7)	=LN(A7)	3	2	=(B7-C7)/D7	=LOGNORMDIST(A7,C7,D7)	=NORMSDIST(E7)

If X is a Lognormal random variable and x is a possible value of this random variable, then LN(X) is assumed to have a normal distribution. If the distribution of LN(X) is normal with mean mu and standard deviation sigma, then you can call LOGNORMDIST(x, mu, sigma) to return the cumulative probability associated with x, in other words, the cumulative probability associated with LN(x) for a Normal random variable with mean mu and standard deviation sigma. Therefore, effectively, when you call LOGNORMDIST(x, mu, sigma), Excel returns the value that is computed by NORMSDIST((LN(x) - mu)/sigma).

In the example on the worksheet, 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: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 Excel

Accuracy of NORMSDIST (and therefore the accuracy of LOGNORMDIST) has been improved in Excel 2003. In earlier versions, 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, more than sufficient for most practical examples.

Results in Excel 2003

The computational procedure for NORMSDIST in Excel 2003 uses two different procedures depending on the standardized value, z, 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 used in earlier versions of Excel. Typical accuracy is now 14 to 15 decimal places.

Conclusions

There are rare occasions where you might demand accuracy better than 7 decimal places. On such occasions, the Excel 2003 versions of LOGNORMDIST and NORMSDIST will give superior performance; for all other computations involving LOGNORMDIST and NORMSDIST, you will not notice a difference between Excel 2003 and earlier versions.

For more information about LOGNORMDIST, click Microsoft Excel Help on the Help menu, type lognormdist in the Search for box in the Assistance pane, and then click Start searching to view the topic.