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.
LOGINV(p, mu, sigma)
Example of usageTo 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.
|x||NORMDIST(x, mu, sigma, TRUE)||EXP(x)||LOGNORMDIST||LOGINV|
|=$B$1 - 2 * $B$2||=NORMDIST(A5, $B$1, $B$2, TRUE)||=EXP(A5)||=LOGNORMDIST(C5, $B$1, $B$2)||=LOGINV(D5, $B$1, $B$2)||=EXP($B$1 + $B$2 * NORMSINV(D5))|
|=$B$1 - $B$2||=NORMDIST(A6, $B$1, $B$2, TRUE)||=EXP(A6)||=LOGNORMDIST(C6, $B$1, $B$2)||=LOGINV(D6, $B$1, $B$2)||=EXP($B$1 + $B$2 * NORMSINV(D6))|
|=$B$1||=NORMDIST(A7, $B$1, $B$2, TRUE)||=EXP(A7)||=LOGNORMDIST(C7, $B$1, $B$2)||=LOGINV(D7, $B$1, $B$2)||=EXP($B$1 + $B$2 * NORMSINV(D7))|
|=$B$1 + $B$2||=NORMDIST(A8, $B$1, $B$2, TRUE)||=EXP(A8)||=LOGNORMDIST(C8, $B$1, $B$2)||=LOGINV(D8, $B$1, $B$2)||=EXP($B$1 + $B$2 * NORMSINV(D8))|
|=$B$1 + 2 * $B$2||=NORMDIST(A9, $B$1, $B$2, TRUE)||=EXP(A9)||=LOGNORMDIST(C9, $B$1, $B$2)||=LOGINV(D9, $B$1, $B$2)||=EXP($B$1 + $B$2 * NORMSINV(D9))|
- 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.
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:
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 ExcelLOGINV(p, mu, sigma) is found by evaluating the following:
- The accuracy of NORMSDIST
- The effectiveness of the search process
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 ExcelExcel 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.
ConclusionsMany 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).
ID do Artigo: 828335 - Última Revisão: 29 de abr de 2008 - Revisão: 1