When you’re working with small samples in Excel — less than 30 or 40 items — you can use what’s called a student t-value to calculate probabilities rather than the usual z-value, which is what you work with in the case of normal distributions. Excel provides six t-distribution functions.
T.DIST: Left-tail Student t-distribution
The T.DIST function returns the student’s left-tailed distribution and uses the syntax
=T.DIST(x,deg_freedom,cumulative)
where x equals the t-value, deg_freedom equals the degrees of freedom, and cumulative is a logical value that determines whether the function returns cumulative distribution value or a probability density. You set the cumulative argument to 0 to return a probability density and to 1 to return a cumulative distribution.
For example, to calculate the left-tailed probability density of the t-value 2.093025 given 19 degrees of freedom, you use the following formula:
=T.DIST(2.093025,19,0)
which returns the value 0.049455, or roughly 5-percent.
Student t-distribution measures let you estimate probabilities for normally distributed data when the sample size is small (say, 30 items or fewer). You can calculate the degrees of freedom argument by subtracting 1 from the sample size. For example, if the sample size is 20, the degrees of freedom equal 19.
T.DIST.RT: Right-tail Student t-distribution
The T.DIST.RT function returns the student’s right-tailed distribution and uses the syntax
=T.DIST.RT(<i>x</i>,<i>deg_freedom</i>)
where x equals the t-value and deg_freedom equals the degrees of freedom. For example, to calculate the right-tailed probability density of the t-value 2.093025 given 19 degrees of freedom, you use the following formula:
=T.DIST.RT(2.093025,19)
which returns the value , or roughly 2.5-percent.
T.DIST.2T: Two-tail Student t-distribution
The T.DIST.2T function returns the two-tailed student t-distribution and uses the syntax
=T.DIST.2T(<i>x</i>,<i>deg_freedom</i>)
where x equals the t-value and deg_freedom equals the degrees of freedom. For example, to calculate the two-tailed probability density of the t-value 2.093025 given 19 degrees of freedom, you use the following formula:
=T.DIST.2T(2.093025,19)
which returns the value 0.049999, or roughly 5-percent.
T.INV: Left-tailed Inverse of Student t-distribution
The T.INV function calculates the left-tailed inverse of a student t-distribution. The function uses the syntax
=T.INV(probability,deg_freedom)
where probability is the probability percentage and deg_freedom equals the degrees of freedom. To calculate the t-value given a 5-percent probability and 19 degrees of freedom, for example, use the following formula:
=T.INV(0.05,19)
which returns the t-value -1.729132 .
T.INV.2T: Two-tailed Inverse of Student t-distribution
The T.INV.2T function calculates the two-tailed inverse of a student t-distribution. The function uses the syntax
=T.INV.@t(probability,deg_freedom)
where probability is the probability percentage and equals the degrees of freedom. To calculate the two-tailed t-value given a 5-percent probability and 19 degrees of freedom, for example, use the following formula:
=T.INV.2T(0.05,19)
which returns the t-value -2.093024.
T.TEST: Probability two samples from same population
The T.TEST function returns the probability that two samples come from the same populations with the same mean. The function uses the syntax
=T.TEST(array1,array2,tails,type)
where array1 is a range reference holding the first sample, array2 is a range reference holding the second sample, tails is either the value (representing a one-tailed probability) or 2 (representing a two-tailed probability), and type tells Excel which type of t-test calculation to make.
You set type to 1 to perform a paired t-test, to 2 to perform a homoscedastic test (a test with two samples with equal variance), or to 3 to perform a heteroscedastic test (a test with two samples with unequal variance).
