Excel statistical functions: LOGINV

This article describes the LOGINV function in Microsoft Excel. The article also discusses an improvement in Microsoft Office Excel 2003 and in later versions of Excel that may affect results in extreme cases when compared with earlier versions of Excel.

LOGINV(p, mu, sigma) is the inverse function for LOGNORMDIST(x, mu, sigma). For any particular x, LOGNORMDIST(x, mu, sigma) returns the probability that a Lognormally distributed random variable with parameters mu and sigma is less than or equal to x. If X is a random variable with this distribution, LN(X) has a Normal distribution with a mean of mu and a standard deviation of sigma.

The LOGINV(p, mu, sigma) function returns the value x for which LOGNORMDIST(x, mu, sigma) returns p. Therefore, LOGINV is evaluated by a search process that returns the appropriate value of x. The relationship between LOGINV and NORMSINV is exploited (see below) to find the appropriate value of NORMSINV and to convert the result to the result of LOGINV.

Syntax

where "p" is a probability with 0 < p < 1, and where "mu" and "sigma" are numeric parameters of the Lognormal distribution. Because sigma represents standard deviation, it must be strictly positive.

Example of usage

To illustrate the LOGINV function, create a blank Excel worksheet, copy the following table, select cell A1 in your blank Excel worksheet, and then paste the entries so that the table fills cells A1:F9 in your worksheet. Note After you paste the contents of this table in your new Excel worksheet, click Paste Options next to the selected text, and then click Match Destination Formatting. With the text still selected, use one of the following procedures, as appropriate for the version of Excel that you are running:

• In Microsoft Office Excel 2007, click the Home tab, click Format in the Cells group, and then click AutoFit Column Width.
• In Excel 2003, point to Column on the Format menu, and then click AutoFit Selection.

You can experiment by changing values of mu and sigma in cells B1:B2. Entries in A5:A9 are then 2 sigmas below the mean, 1 sigma below the mean, the mean, 1 sigma above the mean, and 2 sigmas above the mean respectively. This example assumes that you have not changed mu and sigma from their original values of 5 and 3 respectively.

Entries in cells A5:F5 are explained later in this article. Entries in rows 6 through 9 are completely analogous to those in row 5 but contain different values because of the different values in A6:A9.

Cell B5 returns the cumulative probability of a value less than -1 for a random variable with a Normal distribution with a mean of 5 and a standard deviation of 3. Cell C5 gives EXP(-1), which is 0.368. Because of the inverse relationship between EXP and LN, if you evaluate LN(C5) (not shown), you receive the value -1 in A5. In D5, evaluate LOGNORMDIST(0.368, 5, 3). If X has a Lognormal distribution, LN(X) has a Normal distribution with the specified mean of 5 and a standard deviation of 3. Therefore, the value in B5 should (and does) equal the value in D5. In other words:The first step follows from the fact that if X has a Lognormal distribution, LN(X) has a Normal distribution. The second step substitutes the value of C5, and the last step recognizes that LN(EXP(-1)) = -1 because of the inverse relationship between LN and EXP.

Cells D5 and E5 illustrate the inverse relationship between LOGNORMDIST and LOGINV. Cell F5 gives the formula that is used to evaluate LOGINV (which is illustrated at the beginning of the next section) and therefore shows an alternative way to obtain the same result.

Results in earlier versions of Excel

LOGINV(p, mu, sigma) is found by evaluating the following: Therefore, the numeric accuracy of LOGINV depends primarily on the numeric accuracy of the NORMSINV function. For more information about improvements to the NORMSINV function, click the following article number to view the article in the Microsoft Knowledge Base: NORMSINV is evaluated through a search process that repeatedly evaluates NORMSDIST and continues until it finds a value of x with NORMSDIST(x) that is "acceptably close" to p. Therefore, the accuracy of NORMSINV depends on two factors:

• The accuracy of NORMSDIST
• The effectiveness of the search process

The search process was made more effective in Microsoft Excel 2002. For versions of Excel that are earlier than Excel 2002, "acceptably close" meant a value of x such that NORMSDIST(x) was within about 10^(-7) of the required p value.

Excel 2002 refined the binary search process. The search continued until a found value of x was equal to the required p value within the limits of the finite precision arithmetic in Excel. This implies a tolerance of about 10^(-14) or 10^(-15).

However, inaccuracies in NORMSDIST remained in Excel 2002 so that for some values of p, the advantage of a refined search process was lost because of these inaccuracies.

Results in Excel 2003 and in later versions of Excel

Excel 2003 and later versions of Excel improved the accuracy of NORMSDIST. Therefore, NORMSINV for Excel 2002 is more accurate than NORMSINV for all earlier versions (because of a better search process), and NORMSINV for Excel 2003 and for later versions of Excel is more accurate still than NORMSINV for Excel 2002. Because LOGINV basically calls NORMSINV, these same comments apply to LOGINV.

Conclusions

Many inverse functions have been improved for Excel 2003 and for later versions of Excel. Some functions have been improved for Excel 2003 and for later versions of Excel only by refining the search process. Included in this set of inverse functions are BETAINV, CHIINV, FINV, GAMMAINV, and TINV. No modifications were made to the respective functions that are called by these inverse functions: BETADIST, CHIDIST, FDIST, GAMMADIST, and TDIST.

Additionally, Excel 2002 refined the search process. Excel 2003 and later versions of Excel also improved the accuracy of NORMSDIST (which is called by NORMSINV). These changes affect NORMINV and LOGINV (which call NORMSINV) and NORMDIST and LOGNORMDIST (which call NORMSDIST).