Excel's RSQ Function - Unveiling the Power of Pearson Correlation Moment

Explore the RSQ function, a key player in the realm of statistical functions within Excel.

Description: Returns the square of the Pearson correlation coefficient based on data points in known_y's and known_x's.

Syntax: RSQ(known_y's,known_x's)

Specifics:

- known_y's: Array or data range, a mandatory parameter.

- known_x's: Array or data range, a mandatory parameter.

Note:

- Input values must be numbers, names, arrays, or references containing numbers.

- Logical values and text-formatted numbers directly entered into the argument list -> still considered for calculation.

- Text or error values that cannot be converted to numbers -> cause the function to error.

- If the argument is a reference array containing text or logical values -> these values are ignored, but 0 is still counted.

- If known_y's and known_x's are empty or have different data points -> the function returns the #N/A error value.

- If known_y's and known_x's contain only 1 data point -> the function returns the #DIV/0 error value.

- The equation for the Pearson correlation coefficient, r, is given by:

r=∑(x−¯¯¯x)(y−¯¯¯y)√∑∑(x−¯¯¯x)

Where x and y represent the sample means AVERAGE(known_x’s) and AVERAGE(known_y’s).

Example:

Calculate the square of the Pearson correlation coefficient for values in the data table below:

- In the cell where you want the result, enter the formula: =RSQ(C6:C10,D6:D10)

- Press Enter -> the square of the Pearson correlation coefficient is:

- In case the number of elements in arrays x and y is not equal -> the function returns the error value #N/A

- In the scenario where arrays x, y have only 1 element each -> the function returns the error value #DIV/0.

Here is the guidance and some specific examples when using the RSQ function in Excel.

Wishing all of you great success!