How to use LOGNORM.INV function in Excel?

The `LOGNORM.INV` function in Excel is used to calculate the inverse of the lognormal cumulative distribution function at a given probability. In simpler terms, it gives you the value of the variable at which the cumulative probability matches a given probability, assuming the data follows a lognormal distribution.

Here’s the syntax for the `LOGNORM.INV` function:

LOGNORM.INV(probability, mean, standard_dev)

Here’s a breakdown of each argument:

probability: This is the probability corresponding to the lognormal distribution. It must be a value between 0 and 1 (exclusive).
mean: This is the mean (average) of the natural logarithm of the lognormally distributed data.
standard_dev: This is the standard deviation of the natural logarithm of the lognormally distributed data.

Steps to use `LOGNORM.INV` in Excel:

Open Excel: Launch Microsoft Excel on your computer.
Enter Data: If you have a data set for which you want to calculate the inverse lognormal, make sure it’s entered in your worksheet.
Select a Cell: Click on the cell where you want the result to appear.
Enter the Function:

Start by typing `=` followed by the function name, `LOGNORM.INV`.
Enter the function arguments. For example, `=LOGNORM.INV(0.95, 0, 1)` calculates the inverse of the lognormal distribution at a 95% probability, assuming the mean of the logarithms is 0 and the standard deviation is 1.

Press Enter: After completing the function, press the Enter key to execute it. The result will appear in the selected cell.

Example:

Assume you have the following data:

Probability: 0.95
Mean of log: 1.5
Standard deviation of log: 0.4

You would enter the function as:

=LOGNORM.INV(0.95, 1.5, 0.4)

This formula will return the value from the lognormal distribution for which 95% of the population is below it, given the specified mean and standard deviation of their natural logs.

Points to Note:

Ensure that the probability is between 0 and 1, otherwise Excel will return an error.
Both the mean and standard deviation should be calculated as the statistical measures of the logarithm of your data points.
If your data doesn’t strictly follow a lognormal distribution, the result may not be meaningful.

This function can be particularly useful in financial modeling, reliability engineering, and other fields where lognormal distributions are commonly applied.

facebooklinkedin