How to Use T Distribution in Excel

Introduction: How to Use T Distribution in Excel

About: A simple guy from the mountains with a love for math, statistics and everything technology

This guide provides a simple explanation and breakdown of how to use T Distribution in Excel. The guide explains how to install the data analysis toolpak and provides the excel syntax for six type of T Distribution functions, namely: Left-tail T Distribution, Right-tail T Distribution, One-Tail T Distribution, Two-Tail T Distribution, Left-tailed Inverse of Student t-distribution and Two-tailed Inverse of Student t-distribution

Step 1: Introduction

T-Distribution is one of the basic and core concepts of beginner level statistics and probability alongside standard normal distribution and Z Table. When students start out learning T-Distribution, they are given a pre-calculated T Table which they must look up to, to solve the questions and problem statements which is great for absolute newbies but as one can see, the student is restricted to the values given in the table and might also fail to understand where the values emerge from. Hence when it comes to real life application and if the problem statement includes values outside of the table, the students find themselves in a bind. A very simple solution to overcome this is by using Excel. Excel comes pre-installed with various functions which helps students calculate the T Distribution for various types of distributions and a variety of values even outside the pre-made tables.

Step 2: The T-distribution Functions in Excel

Excel provides with six different T-Distribution functions. Whenever your sample size if below 30 to 40, making use of these functions over a Z Table function is recommended. One can choose between an left-tail or right-tail t distribution, an one tail or two-tail t distribution and an inverse one-tail or two-tail distribution

Step 3: The Data Analysis Toolpak

Some versions of Excel come installed with the Data analysis tookpak whereas for some versions the Data analysis toolpak needs to be installed so as to perform the t-tests. To install this go to Data in excel's menu bar, and choose the Data Analysis option in the analyse section. And good way to check if you have the data pack installed is to just type and press enter for any one of the t distribution functions below and if you get the correct answers it means the data analysis toolpak is already installed.

Step 4: Left-tail Student T-distribution

We will use the T.DIST function provided by excel to return the left-tailed t-distribution. The syntax for the same is given as

=T.DIST(x,deg_freedom,cumulative)

where x is the t-value and deg_freedom is the degrees of freedom. Let's take for example, that you want to calculate the t distribution for left tailed distribution where x = 2.011036 and the deg_freedom = 20

=T.DIST(2.011036,20,0)

which returns the value 0.056974121

Step 5: Right-tail Student T-distribution

We will use the T.DIST.RT function provided by excel to return the right-tailed t-distribution. The syntax for the same is given as

=T.DIST.RT(x,deg_freedom)

just like the previous formula here as well x equals the t-value and deg_freedom equals the degrees of freedom except there is no cumulative here. Just substitute the value of x and degrees of freedom in the above syntax and you will get your value

Step 6: Two-tail Student T-distribution

We will use the T.DIST.2T function provided by excel to return the two-tailed t-distribution. The syntax for the same is given as

=T.DIST.2T(x,deg_freedom)

Nearly identical as the previous syntax except RT is replaced by 2T

Step 7: T.INV: Left-tailed Inverse of Student T-distribution

We will use the T.INV function provided by excel to return the left-tailed inverse of t-distribution. The syntax for the same is given as

=T.INV(probability,deg_freedom)

in left-tailed inverse, instead of x we substitute the value of probability percentage denoted as probability in the syntax.

For example let's say we have the probability percentage of 7-percent and the degrees of freedom is 20. To calculate the t-value the syntax will look like

=T.INV(0.07,20)

which returns the t-value as -1.536852112

Step 8: T.INV.2T: Two-tailed Inverse of Student T-distribution

We will use the T.DIST function provided by excel to return the left-tailed t-distribution. The syntax for the same i given as

=T.INV.2T(probability,deg_freedom)

almost same as the previous formula except for the addition of 2T ahead of the INV function in the syntax

Be the First to Share

    Recommendations

    • Backyard Contest

      Backyard Contest
    • Summer Fun: Student Design Challenge

      Summer Fun: Student Design Challenge
    • Fruit and Veggies Speed Challenge

      Fruit and Veggies Speed Challenge

    Comments