New Contributor

# Data Analysis Correl function and correlation

What is the difference between Correl and Correlation? Results are different. Correl gives the same result as R in regression analysis.

2 Replies

# Re: Data Analysis Correl function and correlation

CORREL is a function. It returns the correlation coefficient of two arrays of the same size.

The result is dynamic: if you change values in the arrays, the function result will automatically be updated.

The Correlation tool in Data Analysis (if you have activated the Analysis ToolPak add-in) returns an array of correlation coefficients of each pair of columns (or rows) in the input range.

So if the input range has 2 columns, you'll get a 2x2 array.

The element in the lower left corner of the array will be identical to the result of CORREL.

The diagonal will contain 1s, since the correlation coefficient of a column with itself is 1.

If the input range has 3 columns, you'll get a 3x3 array, etc.

The result is static: if you change values in the input range, the output of Correlation won't change. You'll have to run Data Analysis > Correlation again to update the output.

# Re: Data Analysis Correl function and correlation

Thank you Hans It was very useful.@Hans Vogelaar