EDUCBA

Home » Excel » Excel Resources » Statistical Functions in Excel » Lognormal Distribution in Excel

Lognormal Distribution in Excel

Lognormal Distribution in Excel (Table of Contents)

Lognormal Distribution in Excel

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

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Lognormal. Dist function is the latest version of the Lognorm.dist function, which is available in the earlier version of Excel 2007.  Lognormal. Dist function arrived in Excel 2010.

The function of Lognormal Distribution:

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

Function of Lognormal Distribution

Arguments of Syntax

  • X (Mandatory Argument) – The value on which the user wishes to evaluate the Lognormal function should be greater than zero (x>0).
  • Mean (Mandatory Argument) – It is an arithmetic mean value of ln(x).
  • Standard_dev (Mandatory Argument) – It is the value of the standard deviation of ln(x).
  • Cumulative (Optional Argument) – It will determine the form of the function; it is a logical value. By default, it will be FALSE; if a user does not provide any value, it will be considered FALSE.

1. TRUE – Lognormal function will return the cumulative distribution function on selecting TRUE.

2. FALSE– Lognormal function will return the probability distribution function on selecting FALSE.

Note: All argument should be numeric value only; otherwise, they will return an Error.

How to Use Lognormal Distribution in Excel?

The Lognormal Distribution in Excel is very simple and easy. Let’s understand how to use the Lognormal Distribution in Excel with some examples.

You can download this Lognormal Distribution Excel Template here – Lognormal Distribution Excel Template

Example #1

A user has a value for x=8, Mean(x)=7, and the standard deviation of ln(x)=2.4; the user wants to calculate cumulative distribution.

  • Open an Excel Sheet and Calculate the Lognormal Distribution.

 Lognormal Distribution in Excel Example 1-1

  •  Write the formula for Lognormal Distribution function,

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

 Lognormal Distribution in Excel Example 1-2

  • Select the respective value from the Data table, x=8, Mean(x)=7, Standard deviation=2.4, and Cumulative value will be TRUE as the user wants to calculate the function for the Cumulative Lognormal Distribution Function.

Lognormal Distribution in Excel Example 1-3

  • Click on the Enter key.

Lognormal Distribution in Excel Example 1-4

Summary of Example #1

As the user has given instruction to calculate cumulative lognormal distribution function for the x=8, Mean=7, Standard Deviation=2.4 and Cumulative = TRUE, so the result is 0.020170865, which is the final Lognormal Distribution for the cumulative distribution function.

Example #2

A user has a Value for x=8, Mean (x)=7 and Standard deviation=2.4, Now we calculate Probability Distribution.

  • Go to Excel, where the user wants to Calculate the Lognormal Distribution.

Lognormal Distribution in Excel Example 2-1 

  • Select the respective value from the User’s Table, x=8, Mean(x)=7, Standard deviation=2.4, and Probability Value will be FALSE. Calculate the function for the Probability Lognormal Distribution Function.

Log Distribution Example 2-2

  • Click on the Enter key.

Log Distribution Example 2-3

Summary of Example #2

As the user has given instruction to Probability Lognormal Distribution function for the Value x=8, Mean (x)=7, Standard Deviation=2.4, and Cumulative = FALSE, and the result is 0.002540039, which is the final Lognormal Distribution for the Probability Distribution Function.

Example #3

A user has Stock value for x=4, Mean(x)=3.5 and Standard Deviation of ln(x)=1.2, Now calculate Cumulative Distribution.

  • Go to Excel and calculate the Lognormal Distribution.

Log Distribution Example 3-1

  • Write a formula for the Lognormal Distribution function.
  • Select the respective value from the user’s table, Stock Value(x)=4, Mean of In(x)=3.5, Standard deviation In(x)=1.2 and Cumulative value will be TRUE. Now we will calculate the function for the cumulative lognormal distribution function.

Log Distribution Example 3-2

  • Click on the Enter key.

Log Distribution Example 3-3

Summary of Example #3

As the user has given instruction to cumulative lognormal distribution function for the Stock Value x=4, Mean of In=3.5, Standard deviation=1.2 and Cumulative = TRUE, so the result is 0.039083556, which is the final Lognormal Distribution for the cumulative distribution function.

Example #4

A user has a stock value for x=4, mean of ln(x)=3.5 and standard deviation of ln(x)=1.2, now the user wants to calculate probability distribution.

  • Go to Sheet1 in Excel Sheet where the user wants to calculate the Lognormal Distribution.

 Log Distribution Example 4-1 

  • Select the respective value from the user’s table, x=4, mean=3.5, standard_dev=1.2 and the cumulative value will be FALSE as the user wants to calculate the function for the Probability Lognormal Distribution Function.

 Log Distribution Example 4-2

  •  Click on the Enter key.

Log Distribution Example 4-3

Summary of Example #4

As the user has given instruction to Probability Lognormal Distribution function for the value x=4, mean=3.5, standard_dev=1.2 and cumulative = FALSE, so the result is 0.017617597, which is the final Lognormal Distribution for the probability distribution function.

Use of Lognormal Distribution

The lognormal distribution has a wide application like financial analysis, Real state analysis, medical data analysis and many more, as some mentioned below:

  • The duration of the chess game follows the lognormal distribution.
  • Calculating the long-term return on Stock.
  • Calculating weight and blood pressure.
  • Calculating life or size of the bacteria’s in disinfection, skin area, the height of human beings.
  • The length of hair, nails, teeth, etc.
  • There is an n-number of uses in Economics.

Things to Remember About Lognormal Distribution in Excel

  • The LOGNORM.DIST function is available in Excel 2010 and the latest version of MS Excel, so it is not available in the older version of MS Excel.
  • The user can use the LOGNORMDIST Function in the older version of MS Excel (Ex. Excel 2007).
  • If a user provides x values less than or equal to Zero (x<=0), then the lognormal function will return #NUM! Error.
  • If a user provides standard_dev values less than or equal to Zero (x<=0), then the lognormal function will return #NUM! Error.
  • If a user provides an argument as non-numeric, then the result of the function will be #Value.

Recommended Articles

This has been a guide to Lognormal Distribution in Excel. Here we discussed How to use Lognormal Distribution in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. SUMIF Formula in Excel
  2. Nested IF Formula in Excel
  3. Date Formula in Excel
  4. Excel RATE Formula
0 Shares
Share
Tweet
Share