Share


How To Calculate Correlation In Excel


By gobrain

Jan 1st, 2024

Correlation is a statistical tool that measures the strength and direction of the relationship between two variables. This tool 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 between the two sets of data within Excel.

Formula For Correlation

Before showing calculating correlation in excel let's see matmaticsel formula for calculating it. The formula for the Pearson's correlation coefficient (also known as the 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)

how to calculate correlation in excel

Conclusion

In this brief guide, we have demonstrated the formulas for correlation and how to calculate them using Microsoft Excel, a widely used spreadsheet for data analysis

Thank you for reading.