Excel correlation formula

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-r²)

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

how to do coefficient of correlation in excel

how to interpret correlation analysis in excel