CORREL function - The function returns the correlation coefficient between two data sets in Excel

PEARSON function - The function returns the Pearson product moment correlation coefficient in Excel
RSQ - The function returns the square of the Pearson torque correlation coefficient in Excel

The following article introduces you to the CORREL function - one of the functions in the statistical function group is very popular in Excel.

CORREL function - The function returns the correlation coefficient between two data sets in Excel Picture 1

Description: The function returns the correlation coefficient between two data sets. Correlation coefficient is used to determine the relationship between two attributes.

Syntax: CORREL (array1, array2)

Inside:

- array1: The range of cells containing the values of the first dataset .

- array2: The range of cells containing the values of the second dataset .

Attention:

- If data cells or references contain logical values, text or blank cells -> they are ignored.

- If array1 and array2 have different data points -> the function returns the # N / A error value .

- If array1 and array2 are empty or the standard deviation of the values of 2 data sets is 0 -> the function returns the # DIV / 0 error value !

- The function equation used is:

[C {rm {or}} relleft ({X, Y} right) = frac {{sum {(x - overline x) left ({y - overline y} right)}}} {{sqrt {sum {{{ (x - overline x)} ^ 2} sum {{{(y - overline y)} ^ 2}}}}}}]

Inside:

({overline x}) and ({overline y}): The average value of samples AVERAGE (array1) and AVERAGE (array2).

For example:

Calculate the correlation coefficient between the two data sets in the table below:

CORREL function - The function returns the correlation coefficient between two data sets in Excel Picture 2

- In the cell to calculate enter the formula: = CORREL (C6: C10, D6: D10)

CORREL function - The function returns the correlation coefficient between two data sets in Excel Picture 3

- Press Enter -> the correlation coefficient between 2 data sets is:

CORREL function - The function returns the correlation coefficient between two data sets in Excel Picture 4

- Case 1 of 2 data sets contains blank values -> the function returns the # DIV / 0 error value !

CORREL function - The function returns the correlation coefficient between two data sets in Excel Picture 5

- Where size = 1 -> the function returns the error value #NUM!

Above are instructions and some specific examples when using the CORREL function in Excel.

Good luck!

Lesley Montoya

Update 19 May 2020

You should read it

May be interested

T.DIST - The function returns the Student t-distribution on the left in Excel
t.dist: the function returns the student's t-distribution on the left. functions used in testing the hypothesis of small numbers of data sets. support functions from excel 2010 onwards. syntax: t.dist (x, deg_freedom, cumulative)
T.DIST.2T - The function returns the two-tailed Student distribution in Excel
t.dist.2t: the function returns the two-tailed student's t-distribution. functions used in testing the hypothesis of small numbers of data sets. support functions from excel 2010 onwards. syntax: t.dist.2t (x, deg_freedom)
How to use the NORMDIST function in Excel - Function that returns the distribution in Excel
the normdist function returns a distribution with a specified mean and standard deviation. the normdist function has applications in statistics, including hypothesis testing.
COVARIANCE.P function - The function returns the covariance of a set, the product of the average of degrees of instruction for each pair of data points in Excel
covariance.p function: returns the covariance of the population, the average of the product deviations for each pair of data points in the two data sets syntax: covariance.p (array1, array2)
RANK.EQ function - Function returns the rank of a number in a list of numbers, returns the highest rank when multiple values with the same rank in Excel
rank.eq function: the function returns the rank of a number in a list of numbers whose size is in correlation with other values. if multiple values have the same rank -> the function returns the highest rank. support functions from execl 2010 version onwards.
HYPGEOM.DIST - The function returns the hyperbolic distribution in Excel
hypgeom.dist: the function returns the hyperinfection distribution, using this function for problems with finite sets of vowismooix observations whether or not they are successful. syntax: hypgeom.dist (sample_s, number_sample, population_s, number_pop, cumulative)
How to use the WRAPROWS function in Excel
having trouble with handling large data sets in excel? then learn how to use the wraprows function to split them into more manageable rows.
TRIMMEAN function - The function returns the average of the inner part of a dataset in Excel
trimmean function: the function returns the average of the inside of the data set. the function calculates by removing which percentage of the value is in the first or last boundary of the data set. syntax: trimmean (array, percent)
QUARTILE.EXC function - The function returns the quartile of a dataset without values 0 and 1 in Excel
quartile.exc function: the function returns the quartile of the data set, with percentile values from 0 to 1 excluding 0 and 1. the support function is from excel 2010 onwards. syntax: quartile.exc (array, quart)
How to use the FILTER function in Excel
if you regularly work with data sets in excel, you know how important it is to be able to quickly find the information you need.

CORREL function - The function returns the correlation coefficient between two data sets in Excel

You should read it

May be interested

Office information