t-Test for Independent Groups in Excel
Given a computed value of t, you can look up the critical value of t in a table, or you can get a computer to compute a probability. Just as the computer can compute a value of p given t, it can compute a value of t given p. To those of us used to calculating t and then looking up a critical value to compare it with to determine its probability, this inverse way of doing things seems counterintuitive. Strange though it may seem, this is how you must approach the computation of a t-statistic in Excel.
The first step is to place your data into columns. In the language of traditional experimental research, X1 is the experiential group scores, and X2 is the control group scores. The next step is to compute the probability associated with the t statistic using the TTEST function. Examine the dialog box for this function below:
Note the help text for “Type” at the bottom of the box. To choose which type of t-test you want the probability reported for, select the appropriate number. That is, the way you do all t-tests is the same in excel. You determine the type of test (dependent or independent samples) by the options you choose in the Function Arguments box. Since we are working with independent data, we enter 2 for “two-sample” which is synonymous with “independent.” For tails, enter 2 to let Excel know that we are conducting a two-tailed test. Array1 is the block of cells that contain the values for X1, and Array2 is the block of cells that contain the values for X2.
Note the results in the Figure below. The value of t is computed using the TINV function, which produces the inverse value of a probability of t. This is a fancy way of saying that given the probability of t, it gives you t. Note the number cell H8 in the function box as the last argument for the TINV function. It represents the degrees of freedom, N – 2.
Note that the mean difference was computed by simply placing a subtraction problem in the function box for the cell (70.00 – 52.00). For the Independent t-test, the degrees of freedom is equal to the number of subjects in both groups minus two (N -2).
Excel Tip: The COUNT Function
When you have a large set of data and do not want to take the time to count the value of N, Excel can do it for you using the COUNT function. Simply insert the COUNT function into an empty cell, and highlight the range of cells that you wish to count. If you have data that you want to be counted in multiple columns, then enter them into different “value” boxes in the Function Argument box.
Last Modified: 06/03/2021