Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
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:
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.
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
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.
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.
- Converting Measurement Units: Using the Convert Function in Microsoft Excel
Describes how to use the CONVERT function within Microsoft Excel to convert various types of measurement units to other types of units. Types of conversions include: weight and mass, distance, time, pressure, force, energy , power, magnetism, tempera
- How to Use the VLOOKUP Function in Excel
Covers how to use the VLOOKUP function in Microsoft Excel with examples on the correct syntax to use.
- How to Remove Errors in Excel With the IFERROR Function
Shows a Microsoft Excel user how to apply the IFERROR Function to remove errors in cells.
- How to Use the ABS Function in Excel
Covers how to use the ABS function in Microsoft Excel with examples on the correct syntax to use.
- How to Use the AVERAGE Function in Excel
Shows how to use the AVERAGE function in Excel with application examples.
- How to Use the COUNT Function in Excel
Shows a Microsoft Excel user how to use the count function in a spreadsheet.
To learn more about using functions in Excel I recommend purchasing Excel Formulas & Functions For Dummies.
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