This function returns the quantile (inverse cumulative distribution) of a lognormal distribution, where the natural logarithm of the random variable *x* is normally distributed with specified mean and standard deviation parameters.
If:
p = LOGNORM.DIST(x, mean, standard_dev, TRUE)
Then:
x = LOGNORM.INV(p, mean, standard_dev)
This means that for a given probability *p*, you can calculate the corresponding quantile value *x* from the lognormal distribution. Use this function to work with data that has been logarithmically transformed.
Syntax:
LOGNORM.INV(probability; mean; standard_dev)
Arguments:
- probability (required): The probability value (0 ≤ *p* ≤ 1) associated with the lognormal distribution.
- mean (required): The mean (μ) of the natural logarithm of *x* (i.e., the mean of ln(*x*)).
- standard_dev (required): The standard deviation (σ) of the natural logarithm of *x* (i.e., the standard deviation of ln(*x*)).
Background:
The inverse lognormal distribution function calculates the value *x* such that the cumulative probability up to *x* equals the specified probability *p*. Mathematically, it is expressed as:
where:
- Φ−1(p)Φ−1(p) is the inverse of the standard normal cumulative distribution function (quantile function of the normal distribution).
- *e* is the base of the natural logarithm (~2.71828).
Example:
Calculate LOGNORM.INV() using the following inputs:
- probability = 0.039084 (the cumulative probability associated with the lognormal distribution)
- mean = 3.5 (the mean of ln(*x*))
- standard_dev = 1.2 (the standard deviation of ln(*x*))
The calculation is illustrated in Figure below.
Result:
The function returns the quantile value 4.000025, meaning that there is a 3.9084% probability that a value from this lognormal distribution will be less than or equal to 4.000025.