The Excel RSQ Function
Pearson Product-Moment Correlation Coefficient
The Excel RSQ Function returns the square of the Pearson product-moment correlation coefficient, which is a statistical measurement of the correlation (linear association) between two sets of values.
The Pearson product-moment correlation coefficient for two sets of values, x and y, is given by the formula:
where x and y are the sample means of the two arrays of values.
Related Function:
PEARSON Function
Function Description
The Excel RSQ function calculates the square of the Pearson Product-Moment Correlation Coefficient for two supplied sets of values.
The syntax of the function is:
RSQ( known_y's, known_x's )
Where known_y's and known_x's are two arrays of numeric values that are of equal length.
Note that if any of the values in the known_y's or known_x's arrays are text values, logical values, or refer to empty cells, these values are excluded from the Rsq function calculation.
Rsq Function Example
| A | B | |
|---|---|---|
| 1 | known y's | known x's |
| 2 | 2 | 22.90 |
| 3 | 7 | 33.49 |
| 4 | 8 | 34.50 |
| 5 | 3 | 27.61 |
| 6 | 4 | 19.5 |
| 7 | 1 | 10.11 |
| 8 | 6 | 37.90 |
| 9 | 5 | 31.08 |
Columns A and B of the above spreadsheet on the right contain two arrays of values.
The square of the Pearson Product-Moment Correlation Coefficient of the values in columns A and B of the spreadsheet can be calculated using the Excel Rsq function, as follows:
=RSQ( A2:A9, B2:B9 )
This gives the result 0.71166629.
Note that this result is the square of 0.843603159, which is the Pearson Product-Moment Correlation Coefficient for the two arrays of values.
For further examples of the Excel Rsq function, see the Microsoft Office website.
Rsq Function Errors
If you get an error from the Excel Rsq function this is likely to be one of the following:
Common Errors
| #N/A | - | Occurs if the supplied known_y's and known_y's arrays have different lengths. |
| #DIV/0! | - |
Occurs if either:
|