Excel statistical functions: LOGNORMDIST

This 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.

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 for mu.

Example of usage

Because 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: 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.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 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: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 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 Excel

The 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.

Conclusions

There 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.