Functions with the T-Distribution in Excel

Microsoft’s Excel is useful in performing basic calculations in statistics. Sometimes it is helpful to know all of the functions that are available to work with a particular topic. Here we will consider the functions in Excel that are related to the Student’s t-distribution. In addition to doing direct calculations with the t-distribution, Excel can also calculate confidence intervals and perform hypothesis tests. Functions Concerning the T-Distribution There are several functions in Excel that work directly with the t-distribution. Given a value along the t-distribution, the following functions all return the proportion of the distribution that is in the specified tail. A proportion in the tail can also be interpreted as a probability. These tail probabilities can be used for p-values in hypothesis tests. The T.DIST function returns the left tail of Student’s t-distribution. This function can also be used to obtain the y-value for any point along the density curve.The T.DIST.RT function returns the right tail of Student’s t-distribution.The T.DIST.2T function returns both tails of Student’s t-distribution. These functions all have similar arguments. These arguments are, in order: The value x, which denotes where along the x axis we are along the distributionThe number of degrees of freedom.The T.DIST function has a third argument , which allows us to choose between a cumulative distribution (by entering a 1) or not (by entering a 0). If we enter a 1, then this function will return a p-value. If we enter a 0 then this function will return the y-value of the density curve for the given x. Inverse Functions All of the functions T.DIST, T.DIST.RT and T.DIST.2T share a common property. We see how all of these functions start with a value along the t-distribution and then return a proportion. There are occasions when we would like to reverse this process. We start with a proportion and wish to know the value of t that corresponds to this proportion. In this case we use the appropriate inverse function in Excel. The function T.INV returns the left tailed inverse of Student’s T-distribution.The function T.INV.2T returns the two tailed inverse of Student’s T-distribution. There are two arguments for each of these functions. The first is the probability or proportion of the distribution. The second is the number of degrees of freedom for the particular distribution that we are curious about. Example of T.INV We will see an example of both the T.INV and the T.INV.2T functions. Suppose we are working with a t-distribution with 12 degrees of freedom. If we want to know the point along the distribution that accounts for 10% of the area under the curve to the left of this point, then we enter T.INV(0.1,12) into an empty cell. Excel returns the value -1.356. If instead we use the T.INV.2T function, we see that entering T.INV.2T(0.1,12) will return the value 1.782. This means that 10% of the area under the graph of the distribution function is to the left of -1.782 and to the right of 1.782. In general, by the symmetry of the t-distribution, for a probability P and degrees of freedom d we have T.INV.2T(P, d) ABS(T.INV(P/2,d), where ABS is the absolute value function in Excel. Confidence Intervals One of the topics on inferential statistics involves estimation of a population parameter. This estimate takes the form of a confidence interval. For example the estimate of a population mean is a sample mean. The estimate also possesses a margin of error, which Excel will calculate. For this margin of error we must use the CONFIDENCE.T function. Excel’s documentation says that the function CONFIDENCE.T is said to return the confidence interval using Student’s t-distribution. This function does return the margin of error. The arguments for this function are, in the order that they must be entered: Alpha – this is the level of significance. Alpha is also 1 – C, where C denotes the confidence level. For example, if we want 95% confidence, then we must enter 0.05 for alpha.Standard deviation – this is the sample standard deviation from our data set.Sample size. The formula that Excel uses for this calculation is: M t*s/ √n Here M is for margin, t* is the critical value that corresponds to the level of confidence, s is the sample standard deviation and n is the sample size. Example of Confidence Interval Suppose that we have a simple random sample of 16 cookies and we weigh them. We find that their mean weight is 3 grams with a standard deviation of 0.25 grams. What is a 90% confidence interval for the mean weight of all cookies of this brand? Here we simply type the following into an empty cell: CONFIDENCE.T(0.1,0.25,16) Excel returns 0.109565647. This is the margin of error. We subtract and also add this to our sample mean, and so our confidence interval is 2.89 grams to 3.11 grams. Tests of Significance Excel will also perform hypothesis tests that are related to the t-distribution. The function T.TEST returns the p-value for several different tests of significance. The arguments for the T.TEST function are: Array 1, which gives the first   set of sample data.Array 2, which gives the second set of sample dataTails, in which we can enter either 1 or 2.Type - 1 denotes a paired t-test, 2 a two-sample test with the same population variance, and 3 a two-sample test with different population variances.