Excel CORREL functionIn statistical analysis, many times, you are asked to find the relationship between two properties. However, there are various ways to achieve that, and one of them is by using the inbuilt CORREL function of Excel. In this tutorial, we will take a deeper look at the CORREL function. What is Excel CORREL function?The Excel CORREL function returns the correlation coefficient between 2 datasets. Correlation lies between -1 and 1, called negative correlation and positive correlation. It returns the correlation coefficient of array1 and array2. For example: as a baby grows older, the height or weight increases - implying that there is a relation between the age and height or age and weight, and with the increase in one, the other is also increasing, i.e., positive correlation. CORREL or the correlation coefficient is very helpful to find out the relation between the two properties. If the specified array or reference parameter includes text/string, logical data, or an empty value, those data are ignored automatically. However, this function includes the cells that value zero in its calculation. The CORREL function is categorized under Excel statistical functions. Though this works gives the same output as the Excel Pearson Function, with the only difference that in earlier versions of Excel (before Excel 2003), the Pearson function may throw some rounding errors. Therefore, if you are working with earlier Excel versions, it is advised to use CORREL as it will return a more efficient and accurate output. In later versions, both functions (CORREL and PEARSON) return the same outputs. Note: If correlation coefficient r's value is close to +1, it shows a definite positive correlation, and if the value of r is near to -1, it indicates a definite negative correlation.SyntaxParametersArray1 (required)- This parameter represents the first array for which you want to calculate the correlation value. Array2 (required) - This parameter represents the second array for which you want to calculate the correlation value. It is a set of dependable variable. ReturnsThe CORREL function returns the correlation coefficient of array1 and array2. Things to Remember
ExamplesExample 1: Calculate correlation between Age and Weight for the following set of numbers.
To determine the correlation coefficient value between Age and Height, follow the below-given steps: STEP 1: Add the helper column named "Correlation"
It will look similar to the below image: In this column, we will enter our CORREL formula. Though it's just for our reference if you want you can skip this step and directly move to STEP 2. NOTE: Format the helper column and match it with the first column to make your Excel sheet more attractive.STEP 2: Type the CORREL functionMove the cursor to the second row (cell reference E4) of your helper column and start typing the function = CORREL( It will look similar to the below image: STEP 3: Insert the arguments, array1 and array2
STEP 4: DATEVALUE will return the result
Example 2: In this example, we are given the data set of weekly changes for a stock A in column C and S&P weekly change in column c (refer to the below table). Based on these data values calculate the correlation coefficient of both the data using the CORREL formula in excel.
To determine the correlation coefficient value between the two data set, follow the below-given steps: STEP 1: Add the helper column named "Correlation"
It will look similar to the below image: In this column, we will enter our CORREL formula. Though it's just for our reference if you want you can skip this step and directly move to STEP 2. NOTE: Format the helper column and match it with the first column to make your Excel sheet more attractive.STEP 2: Type the CORREL functionMove the cursor to the second row (cell reference c4) of your helper column and start typing the function = CORREL( It will look similar to the below image: STEP 3: Insert the arguments, array1 and array2
STEP 4: DATEVALUE will return the resultOnce done press the enter key to fetch the output. The CORREL function will analyze both the data of array1 and array2 and will return the correlation coefficient between 2 datasets. In our case it has returned a correlation value -0.309639228. It will look similar to the below image: Example 3: In the below table, we have different height for both the data columns. Let's see what happens if you calculate the correlation between Age and Height for the following set of numbers.
To determine the correlation coefficient value between Age and Height, follow the below-given steps: STEP 1: Add the helper column named "Correlation"
It will look similar to the below image: STEP 2: Type the CORREL functionMove the cursor to the second row (cell reference E4) of your helper column and start typing the function = CORREL( It will look similar to the below image: STEP 3: Insert the arguments, array1 and array2
STEP 4: DATEVALUE will return the result
Refer to the below image for the output: NOTE: The CORREL functions Correlation coefficient value only if both array1 and array2 are of the same length.Correl Function ErrorsWhile working with the CORREL function, if you get an error, this could likely be because of the following reasons:
Next TopicSheet Options in Excel |