Thread: Anyone use Excel for stats?

1. Anyone use Excel for stats?

I'm trying to use Excel to figure t-values and what I get out of it doesn't match the table in my book. I'm using the formula

=TDIST(A2,B2,C2)

where A2 is the numeric value at which to evaluate the distribution, B2 is the degrees of freedom, and C2 is the number of tails. For A2 = .1, B2 = 1, and C2 = 1, Excel outputs 0.468274483, where the book gives 3.078. Obviously I'm doing something wrong

2. Originally Posted by earachefl
I'm trying to use Excel to figure t-values and what I get out of it doesn't match the table in my book. I'm using the formula

=TDIST(A2,B2,C2)

where A2 is the numeric value at which to evaluate the distribution, B2 is the degrees of freedom, and C2 is the number of tails. For A2 = .1, B2 = 1, and C2 = 1, Excel outputs 0.468274483, where the book gives 3.078. Obviously I'm doing something wrong
The output of TDIST(.1,1,1) is the probability Pr(t>.1) for a t variable with degrees for freedom 1. The probability Pr(t>.1) is called a p-value. A probability has value between 0 and 1, so 3.078 cannot be a probability. 3.078 looks like the value of a t variable (such as a t statistic), which would be the first input argument to TDIST.

What is the wording of the problem?

3. These are real basic "find the t-value from the chart" kinds of problems, such as "Determine percentile $P_{5}$ with df = 12," or "Use Table 4 to determine $P( t < -2.093)$ with df = 19." The problem is, the table only has five columns for right-tail areas of .1, .05, .025, .01, and .005. So I see that Excel is giving me the reverse of what I'm expecting - if I plug in 3.078, 1, 1, the output is close to .1, which is the heading of the corresponding column. So to get what I want in Excel, do I need to use the =TINV formula?

4. Originally Posted by earachefl
These are real basic "find the t-value from the chart" kinds of problems, such as "Determine percentile $P_{5}$ with df = 12," or "Use Table 4 to determine $P( t < -2.093)$ with df = 19." The problem is, the table only has five columns for right-tail areas of .1, .05, .025, .01, and .005. So I see that Excel is giving me the reverse of what I'm expecting - if I plug in 3.078, 1, 1, the output is close to .1, which is the heading of the corresponding column. So to get what I want in Excel, do I need to use the =TINV formula?
The first question uses TINV; the second TDIST. Here is the definition of TINV.

Originally Posted by Microsoft Support
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.
To determine the percentile P5 with df = 12, the formula is -TINV(.1,12) because this equals -x where Pr(t>x or t<-x) = .1, which by symmetry of the t distribution means Pr(t<-x) = .05.

To determine Pr(t<-2.093) with df = 19, the formula is TDIST(2.093,19,1) because this equals Pr(t>2.093) = Pr(t<-2.093) and TDIST does not accept negative values for t.