How to use the CORREL function
What is the CORREL function?
The CORREL function calculatesĀ the correlation between two groups of numbers.
What is the correlation in statistics?
Correlation in statistics refers to the relationship between two variables and how closely they vary or change together.
Correlation measures the association or co-movement between two quantitative variables. The output ranges from -1 to 1. Values close to -1 or 1 indicate strong correlation, values near 0 mean weak or no correlation. Positive correlation means variables move in same direction, negative correlation means opposite directions.
Used to identify relationships and make predictions, but not determine cause and effect alone. Does not imply causation - correlation does not necessarily mean direct causality.
What is covariance?
Covariance measures how two random variables or datasets vary together by calculating the average product of their deviations from their individual means. Positive values indicating both tend to be above or below their means together.
What is regression relationships?
Regression relationships describe the correlation between two variables. One variable is modeled as a function of the other using a regression line or curve that best represents the predictive relationship between the variables' values.
CORREL Function Syntax
CORREL(array1, array2,ā¦)
CORREL Function Arguments
array1 | Required. The first set of numbers. |
array2 | Required. The second set of numbers. |
CORREL function example
Formula in cell B12:
The value in cell B12 shows that there is a strong correlation between ice cream sales and temperature.
CORREL function not working
Text, boolean and empty cells are ignored. This can be good and bad, this is bad if you have numbers stored as text and you don't realize it.
#N/A! error is returned if the arguments don't contain the same number of values, in other words, the size of the cell ranges are not equal.
How is the CORREL function calculated
The CORREL function calculates the correlation between two groups of numbers. To calculate the correlation follow these steps:
-
- Calculate the mean of group of numbers named:
x="Temp"
y="Icecream"
For example:
Mean of X = xĢ is calculated in cell C10
Mean of Y = ȳ is calculated in cell D10 - For each data point xi and yi calculate the deviations from the mean.
Deviation of xi = xi - xĢ are calculated in cells E3:E9
Deviation of yi = yi - ȳ are calculated in cells F3:F9 - Multiply the deviations between each data point pair to get their products.
For each pair: (xi - xĢ) * (yi - ȳ) are calculated in cells G3:G9 - Sum all the deviation products.
S = Ī£(xi - xĢ)(yi - ȳ) calculated in cell G10 - Calculate the sum of squared deviations of each dataset.
SSx = Ī£(xi - xĢ)2 calculated in cell H10
SSy = Σ(yi - ȳ)2 calculated in cell I10 - The correlation is the sum of products divided by the square roots of the sum of squares.
Correlation = S / ā(SSx * SSy) calculated in cell I13
- Calculate the mean of group of numbers named:
These steps calculates the correlation coefficient and the same number is shown in cell B14 which is the output from the CORREL function based on cells C3:C9 and D3:D9.
Functions in 'Statistical' category
The CORREL function function is one of many functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form