Skip to Content

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.

Table of Contents

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

ttest data

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

ttest-1

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

ttest-2

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

ttest-3

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.

ttest-4

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

ttest-5

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

ttest-6

 

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