P-Value in Google Sheets

What is P-Value in Google Sheets?

The p-value is a significant calculation in statistics. It is used to find the statistical significance of two data sets. Simply put, the p-value helps find the significance of the results obtained in hypothesis testing. It shows if your data supports a particular hypothesis or if it occurs due to random chance. Usually, when we obtain a p-value less than 0.05, it is considered statistically significant.

How do we calculate the p-value in Google Spreadsheets? The easiest way is to calculate the p-value using the T.TEST function. You can also use functions like CHITEST and CORREL to determine the relationship between two datasets and their p-values. In the example below, we have two groups of scores. We perform a two-tailed t-test assuming equal variances. We enter the following formula.

0=T.TEST(A2:A6, B2:B6, 2, 2)

How to Calculate P-Value in Google Sheets?

To calculate the p-value in Google Sheets, we perform statistical tests like a t-test, chi-squared Test, etc. Let us look at how to find the p-value using Google Sheets for the common test, T.TEST.

Step 1: Input the required data into two columns. We enter two sets of data in columns A and B.

Step 2: In an empty cell, we use the below P value in Google Sheets formula to calculate the p-value:

=T.TEST(A2:A8, B2:B8, tails, type)

Here, we have two sets of independent variables. So, we use the following formula for two independent samples with a two-tailed test.

=T.TEST(A2:A8, B2:B8, 2, 3)

where,

• A2:A8 is the first data set range
• B2:B8 is the second data set range
• tails: 1 for a one-tailed test,  2 for a two-tailed test.
• type: 1 for a paired t-test, 2 for a two-sample equal variance t-test, 3 for a two-sample unequal variance t-test.

Step 3: Press Enter. Once the p-value appears, compare it to 0.05 or the knows significant value.

If the p-value is less than 0.05, we reject the null hypothesis where there is a statistically significant difference. We fail to reject the null hypothesis if the p-value is greater than 0.05.

– T.TEST Syntax

The T.TEST function compares the means of two data sets to determine if there is a statistically significant difference between them.

Syntax of the T.TEST Function:

=T.TEST(data_y, data_x, tails, type)

Parameters:

1. data_y: The range of values for the first data set (group 1).

2. data_x: The range of values for the second data set (group 2).

3. tails: Specifies the type of t-test.

1. One-tailed test
2. Two-tailed test

4. type: Specifies the type of t-test to perform.

1. Paired t-test (for dependent samples).
2. Two-sample equal variance t-test (for independent samples with equal variance).
3. Two-sample unequal variance t-test (for independent samples with unequal variance).

– CHISQ.TEST Syntax

CHISQ.TEST function performs a Chi-Squared test for comparing observed data to expected data to see if there is a significant difference between them.

Syntax of the CHISQ.TEST function:

=CHISQ.TEST(observed_range, expected_range)

Parameters:

• observed_range: The range of cells that contains the observed frequencies
• expected_range: The range of cells that contains the expected frequencies

– CORREL Syntax

The CORREL function finds the correlation coefficient between two sets of data.

Syntax of the CORREL Function:

=CORREL(data_y, data_x)

Parameters:

• data_y: The range of values for the dependent variable.
• data_x: The range of values for the independent variable.

Examples

Example #1 – Calculating P-Value with T.TEST Function

Let’s look at an example of how to use the T.TEST to compare test scores between two different teachers of Science. Here, we enter the scores secured by the students of teacher Jane in Column A. The scores secured by teacher Anne’s students are entered in Column B.

Step 1: To find the p-value, enter the T.TEST function.

=T.TEST(A2:A10, B2:B10, 2, 2)

Here, the scores of the two teachers are in A2:A10 and B2:B10

It is a two-tailed test and we assume equal variance between the groups.

Step 2: Press Enter. It will calculate the p-value.

Result:

1. If the p-value is less than 0.05, as in this case, we reject the null hypothesis, which means the average score of the two groups is significantly different.
2. If the value is greater than 0.05, it means the average score of the sample group is not significantly different from the other average.

However, the actual interpretation should consider the field of study.

Example #2 – Calculating P-Value with CHISQ.TEST Function

As seen before, the P value is a statistical measurement used to determine whether a hypothesis is correct or not. For example, a student studies every day between 6 and 9 p.m. and scores marks in a test. Next week, he studies between 5 and 8 a.m. and scores some scores. Let us calculate the p-value to find out if the hours of study actually matter or if they are just random.

Step 1: Enter the scores for the two times in Columns A and B.

Step 2: Enter the CHISQ.TEST function. The syntax is CHISQ.TEST(actual_range, expected_range).

• actual_range: The range of cells containing the observed values.
• expected_range: The range of cells containing the expected values.

=CHISQ.TEST(A2:A10,B2:B10)

Step 3: Press Enter to find the p-value.

If the p-value is less than 0.05, you would reject the null hypothesis and conclude that the hours studied significantly influence the scores.

If the p-value is greater than or equal to 0.05, you would fail to reject the null hypothesis, meaning there is no significant relationship between the number of hours studied and the scores.

Example #3 – Using CORREL Function

While Google Sheets’ CORREL function calculates the Pearson correlation coefficient, it doesn’t directly calculate the p-value. To find the p-value, you’ll need to use the T.TEST function or perform a calculation using the correlation coefficient and sample size.

Step 1: Enter the data in Google Sheets. We have a data set that shows the Age, Grade, and Score few people in a locality.

Step 2: You must arrange each variable in its column and have a header for each column. A correlation matrix has at least 2 variables. Select cell B12. Now, input the CORREL Function for the first pair of variables.

To calculate the correlation between “Age” and “Grade,” type =CORREL(A2:A9, B2:B9) into your cell B14. It calculates the correlation between the two variables.

Step 3: To calculate the correlation between “Grade” and “Score,” type =CORREL(B2:B9,C2:C9) into the C15 cell. It computes the correlation between these two variables.

Step 4: Now, compute the same for all cells in the matrix.

Let us look at the correlation coefficients. A value close to 1 or -1 indicates a strong positive or negative correlation, while values around 0 indicate a weak correlation.

• The matrix value for grade-age shows a value of around 0.98, indicating a strong positive correlation between age and grade. 
• Similarly, at 0.125, which is nearer zero, shows is minimal correlation between grade and score.

Then use this to compute the t-statistic and p-value for significance.

Important Things to Note

1. A low p-value less than 0.05 shows that the observed results have not likely occurred by chance alone.
2. Always ensure you decide on the significance level before conducting any tests.
3. Be sure to choose the right statistical test for the given data and analysis

Frequently Asked Questions (FAQs)

Download Template

This article must help understand P-Value in Google sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is P-Value in Google Sheets. We learn how to calculate p-value in Google Sheets with examples & working template. You can learn more from the following articles. –

SKEW Function in Google Sheets

INTERCEPT Google Sheets Function

Z TEST in Google Sheets