Manuals/calci/CORREL

CORREL(ar1,ar2)

• $ar1$ and $ar2$ are the set of values.

Description

• This function gives the correlation coefficient of the 1st set($ar1$) of values and 2nd set($ar2$) of values.
• Correlation is a statistical technique which shows the relation of strongly paired variables.
• For example, test average and study time are related; those who spending more time to study will get high marks and Average will go down for those who spend less time for studies.
• There are different correlation techniques to measure the Degree of Correlation.
• The most common of these is the Pearson Correlation Coefficient denoted by $r_{xy}$.
• The main result of a correlation is called the Correlation Coefficient($r$)which ranges from -1 to +1.
• The $r$ value is positive i.e +1 when the two set values increase together then it is the perfect Positive Correlation.
• The $r$ value is negative i.e. (-1) when one value decreases as the other increases then it is called Negative Correlation.
• Suppose the $r$ value is 0 then there is no correlation (the values don't seem linked at all).
• If we have a series of $n$ measurements of $X$ and $Y$ written as $x_{i}$ and $y_{i}$ where $i=1,2,...n$ then the Sample Correlation Coefficient is:

$CORREL(X,Y)=r_{xy}={\frac {\sum _{i=1}^{n}(x_{i}-{\bar {x}})(y_{i}-{\bar {y}})}{\sqrt {\sum _{i=1}^{n}(x_{i}-{\bar {x}})^{2}\sum _{i=1}^{n}(y_{i}-{\bar {y}})^{2}}}}$

• ${\bar {x}}$ and ${\bar {y}}$ are the sample means of $X$ and $Y$.
• This function will give the result as error when

1.$ar1$ and $ar2$ are non-numeric or different number of data points.
2.$ar1$ or $ar2$ is empty
3.The denominator value is zero.

• Suppose $ar1$ and $ar2$ contains any text, logical values, or empty cells, like that values are ignored.

Examples

1. Find the correlation coefficients for X and Y values are given below :X={1,2,3,4,5}; Y={11,22,34,43,56}

=CORREL(A4:A8,B4:B8)=0.99890610723867

1. The following table gives the math scores and times taken to run 100 m for 10 friends:SCORE(X)={52,25,35,90,76,40}; TIME TAKEN(Y)={11.3,12.9,11.9,10.2,11.1,12.5}

=CORREL(A5:A10,B5:B10)= -0.93626409417769

1. Find the correlation coefficients for X and Y values are given below :X={-4,11,34,87};Y={9,2,59,24}

=CORREL(A1:A4,B1:B4)=0.353184665607273

See Also

• COVAR
• FISHER

References

| Correlation