Guide: How to Use the CORREL Function to Calculate the Correlation Coefficient Between Two Data Sets with a Specific Example

To learn how to use the CORREL function in Excel, please follow the article below!

The instructions in this article are performed on a Laptop running Windows 11 with Excel 2016 version. You can also perform similar steps on Excel 2007, 2010, 2013, 2016, 2019, 2021 versions, and on laptops with the same operating system or MacOS laptops with different operations.

Overview of the CORREL function in Excel

What is the CORREL function?

In Excel, the CORREL function is a statistical function that returns the correlation coefficient between two datasets. This correlation coefficient is a measure of the relationship between two variables. It has a value from -1 to 1.

Correlation cases include:

• Correlation coefficient less than -1 or greater than 1: Because the correlation coefficient has a value from -1 to 1, if the result is less than or greater than this range, it means there is an error in the correlation measurement.
• Negative correlation coefficient: Indicates an inverse relationship between two variables, meaning that when one variable increases, the other decreases.
• Positive correlation coefficient: Indicates a direct relationship between two variables, meaning that when one variable increases, the other variable also increases, and vice versa, when one variable decreases, it leads to a decrease in the other variable.
• Correlation coefficient equals 0: A correlation coefficient of 0 indicates no relationship between two variables (or, in other words, the two variables are independent of each other).

Applications of the CORREL function

• Calculate the correlation coefficient between two columns of data in Excel quickly.
• Determine the relationship between variables:

For example, you can use the CORREL function to determine if there is a relationship between sales and customer satisfaction.

• Create a graph in Excel:

For example, you can use the CORREL function to create a graph showing the relationship between temperature and rainfall.

• Test hypotheses:

For example, you can use the CORREL function to test the hypothesis that “There is no relationship between education level and income” is true or false.

Syntax of the CORREL function

The syntax of the CORREL function in Excel is as follows:

=CORREL(array1, array2)

Where:

• Array1: Is the range of cells containing the values of the first dataset.
• Array2: Is the range of cells containing the values of the second dataset.

How to use the CORREL function in Excel

Example: The following table shows the work experience and income (million VND/month) of 8 people. Calculate the correlation coefficient of the numbers in the table using the CORREL function.

Step 1: In the cell where you want to receive the result, enter the CORREL function manually or use the mouse to select the range of cells containing the values.

Step 2: Press Enter and Excel will display the correlation coefficient between the income data column and the work experience data column for you.

With this result, it is easy for you to see that it is a positive coefficient value. Therefore, you can conclude that work experience and income (in your case) have a direct relationship: the more work experience, the higher the income.

Common errors when using the CORREL function and how to fix them

During the process of calculating the correlation coefficient using the CORREL function, you may encounter the following errors:

Error #DIV/0!

• Cause: If your data cell contains an empty value or the standard deviation of the values in the 2 datasets is 0, the function will return the #DIV/0! error value.
• How to fix: Check the values in the table, if any cell is empty, you can add values to it.

Error #N/A!

Cause: If array1 and array2 have a different number of data points, the function will return the #N/A error value. In that case, you are either referencing excess or missing data ranges.

How to fix: You can check again what array1 or array2 you entered into the function and adjust it to have the same quantity for both datasets.

Conclusion

Above is the definition of the CORREL function and how to use the CORREL function in Excel to calculate the correlation coefficient between 2 datasets. Hopefully, these shares will help you have a more efficient learning and working experience.