Table of Contents
The CORREL function in Excel is a useful tool for calculating the correlation coefficient between two sets of data. This function takes two sets of data as input and returns a value between -1 and 1, indicating the strength and direction of the relationship between the two sets of data. This can be helpful in identifying patterns and relationships between variables. To use the CORREL function, simply input the two sets of data as arguments and press enter. The resulting value will provide insight into the relationship between the two sets of data, allowing for a deeper understanding of the data and potential insights for analysis and decision making.
The CORREL function returns the correlation coefficient of two cell ranges. Use the correlation coefficient to determine the relationship between two properties. For example, you can examine the relationship between a location’s average temperature and the use of air conditioners.
Syntax
CORREL(array1, array2)
The CORREL function syntax has the following arguments:
array1 Required. A range of cell values.
array2 Required. A second range of cell values.
Remarks
If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with zero values are included.
If array1 and array2 have a different number of data points, CORREL returns a #N/A error.
If either array1 or array2 is empty, or if s (the standard deviation) of their values equals zero, CORREL returns a #DIV/0! error.
As much as the correlation coefficient is closer to +1 or -1, it indicates positive (+1) or negative (-1) correlation between the arrays. Positive correlation means that if the values in one array are increasing, the values in the other array increase as well. A correlation coefficient that is closer to 0, indicates no or weak correlation.
The equation for the correlation coefficient is:
where
are the sample means AVERAGE(array1) and AVERAGE(array2).
Example
The following example returns the correlation coefficient of the two data sets in columns A and B.
Cite this article
stats writer (2024). “How can I use the CORREL function in Excel to calculate the correlation coefficient between two sets of data?”. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-correl-function-in-excel-to-calculate-the-correlation-coefficient-between-two-sets-of-data/
stats writer. "“How can I use the CORREL function in Excel to calculate the correlation coefficient between two sets of data?”." PSYCHOLOGICAL SCALES, 29 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-correl-function-in-excel-to-calculate-the-correlation-coefficient-between-two-sets-of-data/.
stats writer. "“How can I use the CORREL function in Excel to calculate the correlation coefficient between two sets of data?”." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-correl-function-in-excel-to-calculate-the-correlation-coefficient-between-two-sets-of-data/.
stats writer (2024) '“How can I use the CORREL function in Excel to calculate the correlation coefficient between two sets of data?”', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-correl-function-in-excel-to-calculate-the-correlation-coefficient-between-two-sets-of-data/.
[1] stats writer, "“How can I use the CORREL function in Excel to calculate the correlation coefficient between two sets of data?”," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. “How can I use the CORREL function in Excel to calculate the correlation coefficient between two sets of data?”. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.