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
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