The LOGNORM.INV function in Excel is a powerful tool used to determine the inverse of the lognormal cumulative distribution function. This function is particularly useful in statistical analysis and risk assessment where values are expected to follow a lognormal distribution. By allowing users to calculate the value at which a given probability lies, LOGNORM.INV provides insight into data that is positively skewed and where the logarithm of values is normally distributed.

Syntax

LOGNORM.INV(probability, mean, standard_dev)

• probability: A float representing the cumulative probability for which you want the inverse value.
• mean: A float that denotes the mean of the natural logarithm of the distribution.
• standard_dev: A float indicating the standard deviation of the natural logarithm of the distribution.

Example #1

=LOGNORM.INV(0.95, 2, 0.5)

This function calculates the value at which 95% of the distribution falls below, with a mean of 2 and a standard deviation of 0.5. The result of this calculation is approximately 9.5907.

Example #2

=LOGNORM.INV(0.5, 0, 1)

This version of the function finds the median value for a lognormal distribution with a mean of 0 and a standard deviation of 1. The result is approximately 1.6487, meaning that 50% of the values lie below this threshold.

Example #3

=LOGNORM.INV(0.99, 3, 0.3)

In this example, the function determines the value below which 99% of a lognormal distribution with a mean of 3 and a standard deviation of 0.3 falls. The output of this calculation is approximately 21.3295.

Error handling

• NUM!: This error occurs when the probability is less than or equal to 0 or greater than or equal to 1. Ensure that the probability is within the (0, 1) range.
• VALUE!: This error appears if any of the parameters are non-numeric. Verify that the values supplied are numbers.

Conclusion

The LOGNORM.INV function is an essential tool for analysts and statisticians dealing with skewed distributions. By offering the ability to find the inverse cumulative probability for a lognormal variable, this function enhances the analytical capabilities within Excel, providing clarity and insight into complex data behaviors and trends. Utilizing it effectively can lead to more informed decision-making in various fields including finance, economics, and risk management.