The LOGNORM.INV in Excel and LOGNORM.TERS in Google Sheets functions are utilized for calculating a value corresponding to the cumulative distribution function of a log-normal distribution based on a given probability. These functions are widely used in finance, engineering, and statistics, particularly for modeling asymmetrically distributed data, such as stock prices.

Function Syntax and Usage

The LOGNORM.INV and LOGNORM.TERS functions take three main parameters:

• probability: This is the probability associated with the cumulative distribution function value to be calculated. This value must be between 0 and 1.
• mean: The logarithmic mean of the random variable which, when exponentiated, conforms to a normal distribution (denoted as mu).
• standard deviation: The logarithmic standard deviation of the random variable which, when exponentiated, conforms to a normal distribution (denoted as sigma).

  =LOGNORM.INV(0.5, 0.0, 1.0)  

In this example, the function calculates a log-normal distribution assuming the mean (mu) is 0 and the standard deviation (sigma) is 1. The result is the median value (or the second quartile) calculated as 1.

Practical Applications

Let”s explore how LOGNORM.INV and LOGNORM.TERS can be utilized in two practical scenarios.

Predicting Investment Returns

When estimating the future value of an investment, you might assume that the returns follow a log-normal distribution. For this example, consider an investment with an average return of 10% annually (calculated logarithmically) and a standard deviation of 0.5.

  =LOGNORM.INV(0.9, 0.1, 0.5)  

This function estimates the maximum likely value of the investment return, calculated with a 90% probability.

Estimating Product Lifetime

When estimating product lifetimes, their durations often follow log-normal distributions. For instance, suppose the average logarithmic lifespan of a product is calculated at 3.5 with a standard deviation of 1.2.

  =LOGNORM.INV(0.7, 3.5, 1.2)  

This calculation provides the time duration which is not likely to be exceeded by the product”s lifetime with 70% probability. This information can be useful for setting warranty periods or planning renewal schedules.

Daha fazla bilgi: https://support.microsoft.com/tr-tr/office/lognorm-ters-işlevi-fe79751a-f1f2-4af8-a0a1-e151b2d4f600