The TINV(p, df) function returns the value x for which TDIST(x, df, 2) returns p. Therefore, TINV is evaluated by a search process that returns the appropriate value of x by evaluating TDIST for various candidate values of x until it finds a value of x for which TDIST(x, df, 2) is "acceptably close" to p.
Note 1 The distribution was found by W. S. Gossett, an employee of the Guinness brewery in Dublin, Ireland. He apparently wanted to remain anonymous and suggested that he be referred to as "student."
Example of usageTo illustrate the TINV function, create a blank Excel worksheet, copy the table that follows, select cell A1 in your blank Excel worksheet, and then paste the entries so the table fills cells A1:G11 in your worksheet.
|t-statistic||=(AVERAGE(A2:A6) - B2) / (STDEV(A2:A6) / SQRT(COUNT(A2:A6)))||=(AVERAGE(D2:G6) - B2) / (STDEV(D2:G6) / SQRT(COUNT(D2:G6)))|
- 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.
If the sample size is n (and here n=5), under the null hypothesis, the t-statistic has a t distribution with n-1 degrees of freedom. The formula for the t-statistic is in cell B8, and its value is about 0.984. The value of TDIST in B9 shows that the probability under the null hypothesis of obtaining a t-statistic further from 0 in either direction (because this is a 2-tailed test) is about 0.381.
Note 2 This example comes from the following out-of-print text:
The main criticism of this experiment is its small sample size. If instead you had the 20 observations in D2:G6, you would compute the t-statistic with 19 degrees of freedom in D8, cell D9 would reveal that (under the null hypothesis) a t-statistic value more extreme than that in D8 would occur with probability 0.045. Cell D10 again confirms the inverse relationship between TINV and TDIST. Cell D11 finds the cutoff value for the t-statistic, assuming that the appropriate probability of 0.05 of rejecting the null hypothesis when it is true. In this experiment, you must reject the null hypothesis at this significance level because the t-statistic value, 2.144, exceeds the cutoff value, 2.093.
Results in earlier versions of ExcelTINV(p, df) is found through an iterative process that repeatedly evaluates TDIST(x, df, 2) and returns a value of x such that TDIST(x, df, 2) is "acceptably close" to p. Therefore, accuracy of TINV depends on the following factors:
- The accuracy of TDIST
- The design of the search process and the definition of "acceptably close"
Results in Excel 2003 and in later versions of ExcelNo changes were made to TDIST in Excel 2003 and in later versions of Excel. The only change that affects TINV was to redefine "acceptably close" in the search process to be much closer. The search now continues until the closest possible value of x is found (within the limits of the finite precision arithmetic in Excel). The resulting x should have a TDIST(x, df, 2) value that differs from p by about 10^(-15).
ConclusionsMany inverse functions have been improved for Excel 2003 and for later versions of Excel. Some functions are improved for Excel 2003 and for later versions of Excel 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 following functions that are called by these inverse functions: BETADIST, CHIDIST, FDIST, GAMMADIST, and TDIST.
Additionally, Excel 2003 and later versions of Excel refined the search process for NORMSINV. 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).
Artikel-id: 828340 – senaste granskning 19 sep. 2011 – revision: 1