Article ID: 828340 - View products that this article applies to.
This article describes the TINV function in Microsoft Excel, and 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.
TINV(p, df) is the inverse function for TDIST(x, df, 2). The last argument in TDIST is the appropriate number of tails in the student's (see note 1) t distribution; this is set to 2 throughout this article. For any particular positive value of x, TDIST(x, df, 2) returns the probability that a t-distributed random variable with df degrees of freedom is greater than or equal to x or is less than or equal to –x.
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."
where "p" is a probability with 0 < p < 1, and where df >= 1 is the number of degrees of freedom. Because df is an integer in practice, Excel will truncate it (round it down) to an integer value if you use a non-integer value.
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.
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:
Collapse this tableExpand this table
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:
Bell, C.E., Quantitative Methods for Administration, Irwin, 1977.TINV is called two times in cells B10 and B11. In cell B10, you verify the inverse relationship between TINV and TDIST by calling TINV with the value of TDIST (about 0.381) in cell B9. The result is the value of the t-statistic from B8. In B11, you ask, "How far from 0 would the t-statistic have to be so that the probability of a t-statistic that is even further from 0 was 0.05 under the null hypothesis?" The answer is about 2.78.
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:
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).
Article ID: 828340 - Last Review: September 19, 2011 - Revision: 3.0