How to use LOGNORM.DIST Function in Excel

Lognormal Distribution function comes under the Statistical functions in the MS Excel, which is one of the most important functions for the financial analysis. Lognormal Distribution function is used to calculate the probability or cumulative lognormal distribution for given value x.

Syntax:= LOGNORM.DIST(x,mean,standard_dev,cumulative)

The LOGNORM.DIST function syntax has the following arguments:

  • X     Required. The value at which to evaluate the function.
  • Mean     Required. The mean of ln(x).
  • Standard_dev     Required. The standard deviation of ln(x).
  • Cumulative     Required. A logical value that determines the form of the function. If cumulative is TRUE, LOGNORM.DIST returns the cumulative distribution function; if FALSE, it returns the probability density function.

Example: Let’s look at some Excel LOGNORM.DIST function examples and explore how to use the LOGNORM.DIST function as a worksheet function in Microsoft Excel:

Example 1:

LOGNORM.DIST Function - How to use LOGNORM.DIST Function in Excel

A user has value for x=3, Mean(x)=2.5 and standard deviation of ln(x)=1.02, now the user wants to calculate cumulative distribution and Probability Distribution.

Syntax:  =LOGNORM.DIST(B1,B2,B3,TRUE)

Result:

LOGNORM.DIST Function in Excel 1 - How to use LOGNORM.DIST Function in Excel

Based on the Excel spreadsheet above, the following LOGNORM.DIST examples would return:

Syntax: =LOGNORM.DIST(B1,B2,B3,TRUE)
Result: 0.084734887

 

Syntax: =LOGNORM.DIST(B1,B2,B3,FALSE)
Result: 0.050733586

Example 2:

LOGNORM.DIST Function - How to use LOGNORM.DIST Function in Excel

A user has Stock value for x=9.21, Mean(x)=10 and Standard Deviation of ln(x)=2.5, Now calculate Cumulative Distribution and probability distribution.

Syntax:  =LOGNORM.DIST(B1,B2,B3,TRUE)

Result: 0.000929487

LOGNORM.DIST Function 1 - How to use LOGNORM.DIST Function in Excel

Based on the Excel spreadsheet above, the following LOGNORM.DIST examples would return:

Syntax: =LOGNORM.DIST(B1,B2,B3,TRUE)
Result: 0.000929487

Syntax: =LOGNORM.DIST(B1,B2,B3,FALSE)
Result: 00.00013674

Note:

  1. The LOGNORM.DIST function will truncate all numerical values to integers.
  2. #NUM! error – Occurs when:
    • The argument x given is less than or equal to zero; or
    • The argument standard_dev is less than or equal to zero.
  3. #VALUE! error – Occurs when any of given arguments is non-numeric.
  4. The LOGNORMAL.DIST function was introduced in Excel 2010 and hence is unavailable in earlier versions. For older versions, we can use the LOGNORMDIST function.

 

Add a Comment

Your email address will not be published. Required fields are marked *