One way to quantify the relationship between two variables is to use the Pearson correlation coefficient which is a measure of the linear association between two variables.
It always takes on a value between -1 and 1 where:
- -1 indicates a perfectly negative linear correlation between two variables
- 0 indicates no linear correlation between two variables
- 1 indicates a perfectly positive linear correlation between two variables
To determine if a correlation coefficient is statistically significant you can perform a correlation test, which involves calculating a t-score and a corresponding p-value.
The formula to calculate the t-score is:
t = r√(n-2) / (1-r2)
where:
- r: Correlation coefficient
- n: The sample size
The p-value is calculated as the corresponding two-sided p-value for the t-distribution with n-2 degrees of freedom.
The following step-by-step example shows how to perform a correlation test in Excel.
Step 1: Enter the Data
First, let’s enter some data values for two variables in Excel:
Step 2: Calculate the Correlation Coefficient
Next, we can use the CORREL() function to calculate the correlation coefficient between the two variables:
The correlation coefficient between the two variables turns out to be 0.803702.
This is a highly positive correlation coefficient, but to determine if it’s statistically significant we need to calculate the corresponding t-score and p-value.
Step 3: Calculate the Test Statistic and P-Value
Next, we can use the following formulas to calculate the test statistic and the corresponding p-value:
The test statistic turns out to be 4.27124 and the corresponding p-value is 0.001634.
Since this p-value is less than .05, we have sufficient evidence to say that the correlation between the two variables is statistically significant.
Additional Resources
How to Create a Correlation Matrix in Excel
How to Calculate Spearman Rank Correlation in Excel
How to Calculate Rolling Correlation in Excel
This is Helpful
Add ABS( ) before the t value?
=T.DIST.2T(ABS(C125),C$114-2)
Yes, **adding `ABS()`** around the *t value* in your formula is **correct and important** when performing a **two-tailed t-test** in Excel using `T.DIST.2T`.
### Here’s why:
The function `T.DIST.2T(x, deg_freedom)` in Excel expects a **positive t statistic**, as it calculates the two-tailed p-value by considering both the positive and negative sides of the distribution.
If your calculated t value (e.g., in `C125`) is **negative**, failing to use `ABS()` would result in an error or incorrect result.
### Correct formula:
“`excel
=T.DIST.2T(ABS(C125), C$114-2)
“`
* `ABS(C125)` ensures the t value is always positive.
* `C$114-2` typically represents the degrees of freedom (`n – 2` for correlation).
### Summary:
✅ Yes, add `ABS()` to ensure the correct p-value for a two-tailed test.