How to Use the CORREL Function in Excel - TurboFuture - Technology
Updated date:

How to Use the CORREL Function in Excel

Joshua has work experience in manufacturing, distribution, and aerospace. He received his BBA in accounting from Kent State University.

The CORREL Function

This illustration shows the CORREL function typed in a cell where the result will be the correlation coefficient between two reference ranges. The CORREL function makes it easy to find out whether compared data is negatively or positively related.

This illustration shows the CORREL function typed in a cell where the result will be the correlation coefficient between two reference ranges. The CORREL function makes it easy to find out whether compared data is negatively or positively related.

What is a Correlation Coefficient?

The CORREL function makes it possible to easily calculate a correlation coefficient in Microsoft Excel. Before going into the use of the CORREL function, it's wise to have an understanding of what a correlation coefficient is.

A correlation coefficient (or coefficient of correlation) can range in value from negative one to positive one where the sign indicates whether there is a positive or negative relationship between two sets of data. While the absolute value of this measurement shows the extent of an X and Y relationship, when squared (coefficient of determination) it shows the proportion of the variance of Y explained by knowledge of X and the reverse.

The Purpose of the CORREL Function

Returning the correlation coefficient proves to be very useful when one needs to find the correlation between two arrays of data in a worksheet. This process can take seconds saving time on what is normally time consuming calculations that are prone to human error.

Using the CORREL Function

The CORREL function needs to be inputted in the form of a formula to work properly. To add this formula directly in to a cell, a cell needs to be clicked in and "=CORREL(" needs to be typed. Next, two arrays of data need to be selected. The syntax of the CORREL function can be seen here:

=CORREL(array1, array2)

These two arrays (array1 and array2) have to be the same size. By default, if a reference argument or array of data has text, an empty cell, or logical values, those cells will be ignored. The array data (numbers, range, named range, cell references) that are used to find the arithmetic mean must be entered separated by a comma. This is then followed by closed parenthesis. After the formula is complete, the enter button can be pressed to return the correlation coefficient for the data.

Inserting the CORREL FUNCTION

The CORREL function can be inserted into a cell with the assistance of a function insert tool. To use this tool, a cell must first be selected. Next, the formulas tab is selected and the "More Functions" button on the Excel ribbon needs to be clicked. The statistical selection from the list is chosen and the CORREL option needs to be clicked.

how-to-use-the-correlation-function-in-excel

Next, the arrays of data can be added into the function arguments window by typing them in or by selecting the arrow buttons pointing up on the left side of the array fields. Notice the tips that appear under the array fields. After the arrays are added, the results also appear in the lower left-hand corner of this window. After each array is added, the okay button can be selected.

Functional Arguments Window

The functional arguments window allow you to data into a function with ease. You are given instructions on what type of data is permissible, along with an easy way to insert data from a spreadsheet with the up arrow to the right of the fields.

The functional arguments window allow you to data into a function with ease. You are given instructions on what type of data is permissible, along with an easy way to insert data from a spreadsheet with the up arrow to the right of the fields.

CORREL Function Results

The CORREL function will return a positive number or a negative number. A number closer to positive 1 means that there is a strong positive relationship between the arrays of data. When the result is close to -1 there is a strong negative relationship between the arrays of data. When the result is close to zero there is not a close relationship between the arrays of data.

There will never be a zero relationship because an error will result if this is the case.

References

Microsoft. (n.d.). CORREL function. Retrieved January 5, 2020, from https://support.office.com/en-us/article/correl-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92.

Kazmier, L. J. (2011). Business statistics. New York: McGraw-Hill.

Related Articles

How to Use the COUNT Function in Excel

How to Use the AVERAGE Function in Excel

How to Use the ABS Function in Excel

How to Remove Errors in Excel With the IFERROR Function

How to Use the VLOOKUP Function in Excel

Converting Measurement Units: Using the CONVERT Function

To learn more about using functions in Excel I recommend the following book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2020 Joshua Crowder