Excel Statistical Functions: TINV (828340)

MORE INFORMATION

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".

Syntax

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 Usage

To illustrate the TINV function, create a blank Excel worksheet, copy the table that follows, select cell A1 in your blank Excel worksheet, and then click Paste on the Edit menu so that the entries in this table fill cells A1:G11 in your worksheet:

data	mu0		more data
12.01	12.1		12.01	12.01	12.01	12.01
12.17			12.17	12.17	12.17	12.17
12.16			12.16	12.16	12.16	12.16
12.14			12.14	12.14	12.14	12.14
12.17			12.17	12.17	12.17	12.17
t-statistic	=(AVERAGE(A2:A6) - B2) / (STDEV(A2:A6) / SQRT(COUNT(A2:A6)))		=(AVERAGE(D2:G6) - B2) / (STDEV(D2:G6) / SQRT(COUNT(D2:G6)))
t-dist	=TDIST(B8,4,2)		=TDIST(D8,19,2)
tinv	=TINV(B9,4)		=TINV(D9,19)
	=TINV(0.05,4)		=TINV(0.05,19)

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, point to Column on the Format menu, and then click AutoFit Selection.

Consider a problem of filling bottles automatically at the Guinness brewery (see note 1). (The problem of emptying bottles is left to the reader as an exercise.) If the machine is correctly adjusted, the actual amount of the liquid that is inserted in a bottle is assumed to be Normally distributed with a mean of 12.10 ounces and an unknown standard deviation. If the machine is out of adjustment, a mean quantity that is other than 12.10 applies (see note 2). Cells A2:A6 show actual contents of a sample of 5 bottles. The hypothesized mean is in cell B2. 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: 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 Excel

TINV(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 two factors:

• The accuracy of TDIST.
  
  -and-
• The design of the search process and the definition of "acceptably close."

In rare cases, "acceptably close" in earlier versions of Excel may not be close enough. This is unlikely to affect most users. If you request TINV(p, df), the search continues until a value of x is found for which TDIST(x, df, 2) differs from p by less than 0.0000003.

Results in Excel 2003

No changes were made in Excel 2003 to TDIST. 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).

Conclusions

Many inverse functions have been improved for Excel 2003. Some functions are improved for Excel 2003 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 refined the search process for NORMSINV. Excel 2003 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).

For more information about TINV, click Microsoft Excel Help on the Help menu, type tinv in the Search for box in the Assistance pane, and then click Start searching to view the topic.