Data Analysis with Microsoft Excel: Updated for Office 2007

242 Fundamentals of Statistics

Working with the t Statistic

Excel provides several functions to work with the t distribution. Two of

these are displayed in Table 6-2.

Table 6-2 Two t distribution functions in Excel

Function Description

TDIST(t, df, tails) Returns the p value for the t distribution for a given

value of t, degrees of freedom df, and tails 5 1 (one

tailed) or tails 5 2 (two tailed)

TINV(p, df) Returns the two-tailed t value from the t distribution

with degrees of freedom df for a p value 5 p. For a

one-tailed t value, replace p with 2 3 p.

Let’s use Excel to apply the t distribution to a problem involving textbook

prices. The college administration claims that students should not expect

to spend more than an average of $500 each semester for books. A student

associated with the school newspaper decides to investigate this claim and

interviews 25 randomly selected students. The average spent by the 25 stu-

dents is $520, and the standard deviation of these purchases is $50. Is this

signifi cant evidence that the statement from the administration is wrong?

First, let’s construct our hypotheses.

H 0 : The average cost (m 0 ) of textbooks is $500.

Ha: The average cost of textbooks is not equal to $500.

Now we construct the t statistic tn 21.

tn 215

x2m 0

s/"n

5

5202500

(^50) /" 25

5

20

10

52

To test the null hypothesis with Excel:

1 Open a new blank workbook.

2 In cell A1, type 5 TDIST(2,24,2) and press Enter.

In this example, 2 is the value of the t statistic, 24 is the degrees of

freedom, and we enter 2 because this is a two-tailed test.

The TDIST function returns a p value of 0.05694, so we do not reject the

null hypothesis at the 5% level. Thus we conclude that there is not suffi -

cient evidence that the college administration is underestimating the price