Hypothesis Testing Part II: How to Conduct a Hypothesis Test in Excel and Google Sheet

Where We've Been, Where We're Headed

In this module we will build on the concepts introduced in Hypothesis Testing Part I, where we explored the underlying statistical principles that make hypothesis testing possible. Here, we will introduce the different types of hypothesis tests you can conduct and will provide a step by step guide for how to conduct them in Excel. We follow the Excel Guide with a a description of how to perform hypothesis tests in Google Sheets.

Before beginning this module, we highly recommend that you complete the previous asynchronous modules: Calculating Descriptive Statistics in Excel/Google Sheets and Hypothesis Testing Part I.

Z-Tests

This second qualification -- that you need to know the standard deviation of the population -- is a difficult requirement to meet.

In the previous module, we explained the difference between a population and a sample. In that module, we described that a population consists of everyone who belongs to a defined group. We also described that a sample is a subset of everyone who belongs to a defined group.

The difference between a population and a sample is one of the most important concepts in all of statistics. If we look at the definition of statistics, we'll see why:

Statistics is the practice or science of collecting and analyzing numerical data in large quantities, especially for the purpose of inferring proportions of a whole from those in a representative sample. (Oxford Languages)

From this definition we can surmise that the goal of statistics is to use the values we find in samples to estimate the values inherent within populations.

We use samples because they are much easier (and cheaper) to measure. For example, the U.S government needed 14.2 billion dollars to conduct the most recent census. Despite the incredible resources dedicated to measure the nation’s population, some subgroups within the the U.S. were still undercounted.

By using statistics, we can use much smaller samples to calculate accurate approximations of the U.S population. For example, if we wanted to know the average income of adults in the United States, we could attempt to measure the income of all 258 million adults in the U.S. Alternatively, we could obtain a random sample of 1,000 adults, calculate their average income, and then use that average to estimate the average income of every adult within the United States.

How does this relate to z-tests? It relates because you rarely know the actual standard deviation of a particular value for a population. This means that you are rarely able to use z-tests in your work.

Degrees of freedom are defined as the number of independent pieces of information within a distribution. Degrees of Freedom are highly related to your sample size – the number of people in your dataset -- and they are a notoriously tricky subject in statistics. Luckily, you do not need an in-depth knowledge of Degrees of Freedom to perform a hypothesis test in Excel.

However, if you want to develop an understanding of what they are and why they are important, we recommend starting with the two videos below. If you are feeling extra ambitious, you could also listen to this episode of Quantitude on Information Theory. If you are not interested in learning about degrees of freedom, feel free to skip over these linked resources!

There are four main categories of T-Tests we can conduct. The types are:

1. Dependent Sample (Paired)

2. Independent Sample (Unpaired)

3. Two-Tailed

4. One-Tailed

The test we use depends on the attributes of our data and the analytical question we are asking. Below is an user-friendly guide that explains when, how, and why to use each test.

If we revisit the visualization of the normal distribution and the 68-95-99.7 rule from the last module, we can see that 5% of the observations of a normal distribution are two standard deviations away from the mean. These areas are represented by the green areas of the picture. The visual below shows 2.5% of the observations two standard deviations below the mean and 2.5% of the observations two standard deviations above the mean.

An example of this in your work as a Data Fellow would be if you wanted to conduct a t-test to compare the mean test scores of economically disadvantaged students before and after an RSSP intervention strategy was implemented to see if there was a statistically significant difference in the positive or negative direction.

This is an uncomfortable idea. If our LEA has spent significant time and resources working to implement an RSSP strategy, the hope is that the strategy would improve student learning. Unfortunately, there exists the real possibility that students will actually do worse under the new intervention than they would have done if they were allowed to continue learning under the previous status quo. While we can not fully determine which learning environment is better for students (the status quo or the new intervention) without establishing a control group and a treatment group, we should recognize that student scores may become worse under the new RSSP strategy than they were before the strategy was implemented. And if they are worse, we want to give ourselves the ability to detect that effect. That is only possible if we use a Two-Tailed T-Test.

Compare the image of the distribution in the previous section to the two images featured below, sourced from UCLA.

You will notice that they key difference between the image in the previous section and these two images is that the shaded area that comprises 5% of the distribution is not split between the extreme negative and extreme positive sides of the distribution. Instead, in these images, the 5% area of the distribution is focused completely to the extreme negative side of the mean or to the extreme positive side of the mean.

When we conduct a One-Tailed T-Test, we are effectively saying that we are confident our intervention is only going to have a negative effect OR a positive effect. By conducting a one-tailed t-test, we are giving ourselves a better chance of detecting an effect on one side of the distribution, if an effect actually exists. The catch is that by giving ourselves a greater chance of detecting an effect on one side of the distribution, we make it impossible to detect an effect on the other side of the distribution. We are putting all of our statistical eggs in one basket.

This introduces significant risk into our analysis. While it does provide us with the advantage of increasing the possibility that we are able to detect a positive effect, if it exists, it also eliminates our ability to detect a negative effect, if it exists. Because of this, we strongly encourage you to conduct two-tailed tests when working with your RSSP data. As mentioned above, it is very difficult to be certain that our RSSP interventions are guaranteed to have a positive effect on student performance. If we assume our intervention is having a positive effect on student performance and conduct a one-tailed test, we run the risk of not detecting a negative effect. If our RSSP intervention is influencing students in a negative way, that is information we would want our RSSP team to know.

We finally have the knowledge we need to perform hypothesis tests!

Step 1. Calculate the variance of test scores for Non-ELA and ELA students

• In order to know if we should conduct an independent t-test that assumes equal variance, we first need to find the variances of our two groups and compare them.

• We find the variances of each group by using the simple function =VAR.S()

  • The VAR stands for "variance" and the .S stands for "sample". This means we are calculating the variance for a sample.

  • Inside of the parenthesis, you will enter the range of values for which you want to find the variance. In our case, the range of values is D3:119. Your range may be different if you copied and pasted the data in a different location on your spreadsheet.

  • In total, your function should look like this: =VAR.S(D3:D119)

  • Once you hit enter, the value 27289.28706 should appear. That is the variance for Central Peaks' Non-ELA student test scores.

  • Repeat the process again to find the variance of ELA test scores. If your spreadsheet looks exactly like the Google Sheet attached above, you should use the following function and data range to calculate the variance: =VAR.S(E3:E58)

  • When you press enter, the value 25524.17143 should appear. That is the variance for Central Peaks' ELA student test scores.

Step 2. Perform an F-Test

• An f-test is very similar to a t-test. The null hypothesis for our f-test is that there is no difference in the variances of our two groups. The alternative hypothesis is that there is a difference in the variances of our two groups. We will determine if we reject the null hypothesis if the p-value generated by the f-test is less than .05.

• To perform an f-test, you will use the simple Excel function =FTEST().

• You will then enter the values ranges you want to test.

  • It is recommended that the first range of values you insert into the function is the range with the higher variance. In our case, we will enter the scores of Non-ELA students first and the scores of ELA students second.

  • If your looks exactly like the Google Sheet attached above, your function should look like this: =FTEST(D3:D119, E3:E58)

  • When you hit enter, the number .7955 should appear. This number is our p-value. Because this p-value is greater than .05, we fail to reject the null hypothesis. In other words, we do not have strong enough evidence to claim that the variances of our two groups are statistically different. Or, using a simpler explanation, the variance between Non-ELA and ELA test scores is roughly equal.

  • This means we will choose to conduct a two-tailed t-test that assumes equal variance.

Step 1. Calculate the variance of test scores for Non-EcoDis and EcoDis students.

• In order to know if we should conduct an independent t-test that assumes unequal variance, we first need to find the variances of our two groups and compare them.

• We again find the variances of each group by using the simple function =VAR.S()

  • To find the variance of Non-EcoDis test scores, your function will look like this (if your spreadsheet looks exactly like the Google Sheet attached above): =VAR.S(G3:G96)

  • Once you hit enter, the value 41533.08591 should appear. That is the variance for Central Peaks' Non-EcoDis student test scores.

  • Repeat the process again to find the variance of EcoDis test scores. If your spreadsheet looks exactly like the Google Sheet attached above, you should use the following function and data range to calculate the variance: =VAR.S(H3:H81)

  • When you press enter, the value 6868.82246 should appear. That is the variance for Central Peaks' EcoDis student test scores.

Step 2. Perform an F-Test

• • If your looks exactly like the Google Sheet attached above, your function should look like this: =FTEST(G3:G96, H3:H81)

  • When you hit enter, the number 2.61124E-14 should appear. In this case, the p-value is so low that Excel needed to use scientific notation to express it. In its non-scientific notation form, the number is .00000000261124. Because the p-value is less than .05, we can reject the null hypothesis.

  • This means that we have gathered enough evidence to credibly claim that difference between the variances of our two groups is statistically significant. We will therefore choose to conduct a two-tailed t-test that assumes unequal variance.

The above step-by-step guide was created to help you see exactly how to make the decisions required to conduct t-tests in spreadsheets. Using the knowledge you now have from reviewing the step-by-step guide, you are able to perform t-tests using formulas in Excel AND Google Sheets. The structure of the formula is the same for both platforms and is as follows:

=TTEST(Range 1, Range 2, How Many Tails, Test Type)

For example, if I wanted to conduct a two-tailed independent sample t-test that assumes unequal variance -- similar to the example above -- I would use the formula =TTEST(G3:G96, H3:H81, 2, 3)

If you look closely, the formula contains the exact same elements we used in the Function Argument screen in the step-by-step guide. We simply insert the cell ranges for each of our groups, list the number of tails we want in our test, and insert the test type where

• 1 = Dependent t-test

• 2 = Independent t-test that assumes equal variance

• 3 = Independent t-test that assumes unequal variance.