Article ID: 828340  View products that this article applies to. On This PageSUMMARYThis 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. MORE INFORMATIONTINV(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 tdistributed 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." Syntax
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.Collapse this table
If the sample size is n (and here n=5), under the null hypothesis, the tstatistic has a t distribution with n1 degrees of freedom. The formula for the tstatistic 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 tstatistic further from 0 in either direction (because this is a 2tailed test) is about 0.381. Note 2 This example comes from the following outofprint 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 tstatistic from B8. In B11, you ask, "How far from 0 would the tstatistic have to be so that the probability of a tstatistic 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 tstatistic with 19 degrees of freedom in D8, cell D9 would reveal that (under the null hypothesis) a tstatistic 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 tstatistic, 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 tstatistic 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). PropertiesArticle ID: 828340  Last Review: September 19, 2011  Revision: 3.0
