How To Calculate Correlation In Excel

Correlation is a statistical tool that measures the strength and direction of the relationship between two variables. It is used to determine if changes in one variable are associated with changes in another variable.

On the other hand, Excel is a important software for statistics, so it is important to know how calculate correlation in Excel. Let's get started.

What is the Formula of Correlation?

Before showing how to calculate the correlation in Excel, let's see the mathematical formula for calculating it. The formula for the Pearson's correlation coefficient (also known as Pearson's r) is:

r = cov(X, Y) / (sX * sY)

where:

• X and Y are the two sets of data
• cov(X, Y) is the covariance between X and Y
• sX and sY are the standard deviation of X and Y

How to Calculate Correlation In Excel

Fortunately, we don't need to calculate each steps to reach result because excel has a function called CORREL(array1, array2), where array1 is the first set of data and array2 is the second set of data.

For example, if you have two columns of data, A and B with values ranging from 1 to 10, you can calculate the correlation coefficient between the two columns by using the following formula:

=CORREL(A1:A10, B1:B10)

The result tell us that correlation is 0.921 and it indicates a very strong positive linear relationship between two variables.

Conclusion

In this brief guide, we have showed the formula of correlation and how to calculate it in Excel with the help of CORREL function.

Thank you for reading.