Returns the Pearson-r correlation coefficient[1] of the values in two data sets.
Syntax:
CORREL( x‑data:Range or Array, y‑data:Range or Array )
Formula:
CORREL, r = ( i ∑1n (xi − x) ⋅ (yi − y) ) ∕ ( √ i ∑1n (xi − x)2 ⋅ (yi − y)2 )
where n is the number of values, xi is the i‑th value in the x‑data, x is the arithmetic mean of the x‑data, yi is the i‑th value in the y‑data, and y is the arithmetic mean of the y‑data.
Example:
CORREL(A1:A15, B1:B15)
returns the Pearson-r correlation coefficient of the contents of cells in the range A1:A15
and those in B1:B15
.
Note:
CORREL()
is implemented using a two-pass approach, first independently computing the mean values,
then computing intermediate values (such as the sum of the squares, products) using the difference between each value and the corresponding mean value.
This approach helps avoid many round-off errors and is also employed by Microsoft Excel 2003 and later.
Revisions:
This function was added in Fireworkz 2.00.