- Advanced Excel Functions Tutorial
- Advanced Excel Functions - Home
- Compatibility Functions
- Advanced Excel Functions - Cube
- Database Functions
- Date & Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Advanced Excel Functions - Logical
- Lookup & Reference Functions
- Math & Trignometric Functions
- Statistical Functions
- Useful Resources
- Quick Guide
- Useful Resources
- Discussion
Advanced Excel Statistical - RSQ Function
Description
The RSQ function returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.
Syntax
RSQ (known_y's,known_x's)
Arguments
Argument | Description | Required/Optional |
---|---|---|
Known_y's | An array or range of data points. | Required |
Known_x's | An array or range of data points. | Required |
Notes
The equation for the Pearson product moment correlation coefficient, r, is −
$$r=\frac{\sum \left ( x-\bar{x} \right )\left ( y-\bar{y} \right )}{\sqrt{\sum \left ( x-\bar{x} \right )^2 \sum \left ( y-\bar{y} \right )^2}}$$
Where x and y are the sample means AVERAGE (known_x’s) and AVERAGE (known_y’s)
RSQ returns r2, which is the square of this correlation coefficient.
Arguments can either be numbers or names, arrays, or references that contain numbers.
Logical values and text representations of numbers that you type directly into the list of arguments are counted.
If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
Arguments that are error values or text that cannot be translated into numbers cause errors.
If known_y's and known_x's are empty or have a different number of data points, RSQ returns the #N/A error value.
If one or both of the supplied arrays contain only 1 data point, RSQ returns the #DIV/0! error value.
If the standard deviation of their values in one or both of the supplied arrays is equal to zero, RSQ returns the #DIV/0! error value.
Applicability
Excel 2007, Excel 2010, Excel 2013, Excel 2016