How to Calculate p-Value in Google Sheets

Calculating the p-value is one of the most fundamental skills in all of statistics.

The p-value allows you to show whether the results of an experiment are statistically significant or not.

You can calculate p-values manually, but using a spreadsheet program like Google Sheets will greatly speed up this process.

In this tutorial, I will show you how to calculate the p-value in Google Sheets.

What is p-Value?

The p-value helps data scientists and researchers evaluate whether hypotheses are likely to be true or not.

Scientists will run an experiment and choose a value or range of values and determine a normal expected range of data. They will then run their experiment and calculate the p-value to compare how close their experimental data is to the normal range.

The p-value helps determine the probability that the results of an experiment occurred by random chance.

Important concepts

• A p-value will measure the odds that a difference in your data occurred by random chance.
• The lower the p-value, the more statistically significant the difference is

Usually, a significance level is set to determine how small a p-value must be for the data to be considered statistically significant. Most commonly this level is set to .05. This means that if the p-value is less than .05 the data is considered statistically significant.

How to Calculate p-Value in Google Sheets

To show you how to calculate p-values in Google Spreadsheets, I am going to walk you through an example dataset and show you how to calculate using this data.

Here is the example data we will be using

In this dataset, I have two sets of data for different individuals. I have their body weight and the number of calories consumed per day.

I am going to use this data to show you how to use the T.TEST function in Google Sheets to calculate p-value.

The syntax of the T.TEST function is:

=T.TEST(range1, range2, tails, type)

• range1 – this is the first set of data used in the t-test
• range2 – this is the second set of data used in the t-test
• tails – this tells google sheets the number of distribution tails (1: one-tailed distribution, 2: two-tailed distribution)
• type – The refers to the type of t-test. (1: paired test, 2: two-sample equal variance test, 3: two-sample unequal variance test)

Here are the steps to use the T.TEST function with our example data:

1. In the cell where you want to calculate your p-value, type “=T.Test” and press Tab on your keyboard to enter the formula

2. Select your first range of data and then add a comma

3. Select your second range of data and add a comma

4. Next you will need to enter a number depending on the numbers of tails that are used for distribution. In our example, we have a one-tailed distribution so we put a 1 here.  Add a comma after this.

5. Next, you need to put another number to specify the type of t-test. We put a 3 to use a two-sample unequal variance test. After you’ve entered your number for the type of test, add your closing parenthesis “)” and press Enter on your keyboard

6. The results of your T-test should now appear in the cell you Enter the formula in. This is your p.value.

 

Closing Thoughts

With the T.TEST function in Google Sheets, calculating the p-value is incredibly easy. You just need to input your data, specify the number of distribution tails, and the type of t-test, and then your spreadsheet does all the work for you.

If you are used to calculating p.values manually, this will be a game-changer!

More Google Sheets Tutorials:
How to Calculate Percentages
How to Count Unique Values
How to Calculate Weighted Average
How to Use the AVERAGEIF Function