CORREL function in excel is a statistical function. It is used to find correlation between two variables and gives correlation coefficient having value between +1 and -1. A value of +1 means a perfect directly proportional relation is present and a value of -1 means a perfect inversely proportional relation is present. It is a worksheet function and can be entered into the cell in which value is required.
Array1: 1st range of values
Array2: 2nd range of values
- If the arguments contain text, empty cell, logical values etc. then they are ignored but cell having 0 as value is not ignored.
- If arguments contain different set of data points, #N/A error is displayed.
- If any of the argument is empty, #DIV/0! Error value is displayed.
- Equation used for correlation coefficient is:
Here bar over x and y represents there average values.
- In this example, various values are entered in Column A and Column B , and a correlation between values of Column A and column B are found using the formula =CORREL(A4:A9, B4:B9) which gives 0.9426332927 as the result.