How to Use Correlation Functions in Excel

By Stephen L. Nelson, E. C. Nelson

A helpful set of functions you should be familiar with when you need information about relationships in Excel in the correlation functions. Excel’s correlation functions let you quantitatively explore the relationships between variables.

CORREL: Correlation coefficient

The CORREL function calculates a correlation coefficient for two data sets. The function uses the syntax

=CORREL(array1,array2)

where array1 is a worksheet range that holds the first data set and array2 is a worksheet range that holds the second data set. The function returns a value between −1 (which would indicate a perfect, negative linear relationship) and +1 (which would indicate a perfect, positive linear relationship).

PEARSON: Pearson correlation coefficient

The PEARSON calculates a correlation coefficient for two data sets by using a different formula than the CORREL function does but one that should return the same result. The function uses the syntax

=PEARSON(array1,array2)

where array1 is a worksheet range that holds the first data set and array2 is a worksheet range that holds the second data set. The function returns a value between −1 (which would indicate a perfect, negative linear relationship) and +1 (which would indicate a perfect, positive linear relationship).

RSQ: r-squared value for a Pearson correlation coefficient

The RSQ function calculates the r-squared square of the Pearson correlation coefficient. The function uses the syntax

=RSQ(known_y's,known_x's)

where known_y’s is an array or worksheet range holding the first data set and known_x’s is an array or worksheet range holding the second data set. The r-squared value describes the proportion of the variance in y stemming from the variance in x.

FISHER

The FISHER function converts Pearson’s r-squared value to the normally distributed variable z so you can calculate a confidence interval. The function uses the syntax

=FISHER(r)

FISHERINV

The FISHERINV function, the inverse of the FISHER function, converts z to Pearson’s r-squared value. The function uses the syntax

=FISHERINV(y)