The LOGNORM.INV function in Google Sheets is a statistical tool used to determine the inverse of the cumulative distribution function for a log-normally distributed random variable. This function is particularly useful in financial modeling and risk assessment, where variables often follow a log-normal distribution.

Syntax

LOGNORM.INV(probability, mean, standard_dev)

• probability: A number between 0 and 1 representing the probability associated with the log-normal distribution.
• mean: The mean of the natural logarithm of the log-normally distributed variable.
• standard_dev: The standard deviation of the natural logarithm of the log-normally distributed variable.

Example #1

LOGNORM.INV(0.9, 2, 0.5)

This function calculates the value at the 90th percentile of a log-normally distributed variable where the mean of its logarithm is 2 and the standard deviation is 0.5. An example result could be approximately 10.75.

Example #2

LOGNORM.INV(0.5, 1, 0.25)

This function finds the median value of a log-normally distributed variable with a logarithmic mean of 1 and a standard deviation of 0.25. The expected output might be around 2.71.

Example #3

LOGNORM.INV(0.05, 1.5, 0.75)

Here, this function determines the value corresponding to the 5th percentile for a log-normal distribution with a logarithmic mean of 1.5 and a standard deviation of 0.75. A possible result could be approximately 0.67.

Error handling

• NUM! This error occurs when the probability is not in the range of 0 to 1, or when the parameters lead to an undefined log-normal distribution.
• VALUE! This error arises when the inputs for mean and standard deviation are not numeric values.
• DIV/0! This may occur if the standard deviation is set to zero, causing division by zero when computing the inverse.

Conclusion

The LOGNORM.INV function is an invaluable resource for analysts and statisticians who work with log-normally distributed data. By understanding its parameters and use cases, users can effectively interpret data relevant to financial forecasts and risk analysis, thereby enhancing their decision-making processes.