# RSQ Function in Excel

### Part 1: Introduce

#### Definition

The RSQ function in Excel calculates the Pearson product-moment correlation coefficient square through data points in known_y’s and known_x’s.

#### Purpose

The function is used to determine the quality of the fit of a set of data points to a linear regression. The r-squared value can be interpreted as the proportion of the variance in y attributable to the conflict in x.

#### Syntax & Arguments

syntax
```RSQ(known_y's, known_x's) ```
• known_y’s: Required. An array or range of data points.
• known_x’s: Required. An array or range of data points.

#### Explain the Arguments in the Function

• known_y’s: These are the observed values of the variable you are trying to predict or explain.
• known_x’s: These are the experimental values of the variable you use to indicate the known_y’s.

#### Return Value

The RSQ function returns the square of the Pearson product-moment correlation coefficient, which measures how well the data points fit the linear regression.

#### Remarks

• The arguments must be either numbers or names, arrays, or references that contain numbers.
• If the 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 known_y’s and known_x’s contain only 1 data point, RSQ returns the #DIV/0! Error value.

### Part 2: ExamplesExample 1

• Purpose of Example: Calculate the r-squared value for sales prediction based on advertising spend.
• Data Tables and Formulas:
ABCD
2100500=RSQ(B2:B4, A2:A4)0.867
32001000
43001500
• Explanation: The r-squared value calculated using the RSQ function shows how well our linear model (based on advertising spend) predicts sales. A higher r-squared value indicates a better fit for the model. The result is displayed in column D.

#### Example 2

• Purpose of Example: To calculate the r-squared value for predicting profit based on the number of employees.
• Data Tables and Formulas:
ABCD
1EmployeesProfitR-SquaredResult
2101000=RSQ(B2:B4, A2:A4)1.000
3202000
4303000
• Explanation: The r-squared value calculated using the RSQ function shows how well our linear model (based on the number of employees) predicts profit. A higher r-squared value indicates a better fit for the model. The result is displayed in column D.

#### Example 3

• Purpose of Example: To calculate the r-squared value for predicting revenue based on the number of units sold.
• Data Tables and Formulas:
ABCD
1Units SoldRevenueR-SquaredResult
2505000=RSQ(B2:B4, A2:A4)1.000
310010000
415015000
• Explanation: The r-squared value calculated using the RSQ function shows how well our linear model (based on the number of units sold) predicts revenue. A higher r-squared value indicates a better fit for the model. The result is displayed in column D.

#### Example 4

• Purpose of Example: To calculate the r-squared value for predicting customer satisfaction based on the number of support staff.
• Data Tables and Formulas:
ABCD
1Support StaffSatisfactionR-SquaredResult
2570=RSQ(B2:B4, A2:A4)1.000
31080
41590
• Explanation: The r-squared value calculated using the RSQ function shows how well our linear model (based on the number of support staff) predicts customer satisfaction. A higher r-squared value indicates a better fit for the model. The result is displayed in column D.

#### Example 5

• Purpose of Example: To calculate the r-squared value for predicting website traffic based on the number of blog posts.
• Data Tables and Formulas:
ABCD
1Blog PostsTrafficR-SquaredResult
2101000=RSQ(B2:B4, A2:A4)1.000
3202000
4303000
• Explanation: The r-squared value calculated using the RSQ function shows how well our linear model (based on the number of blog posts) predicts website traffic. A higher r-squared value indicates a better fit for the model. The result is displayed in column D.

#### Example 6

• Purpose of Example: To calculate the r-squared value for sales prediction based on advertising spend only if the advertising spend is above a certain amount.
• Data Tables and Formulas:
ABCDE
2100500=RSQ(B2:B4, A2:A4)0.867=IF(A2>150, C2, “N/A”)
32001000=RSQ(B2:B4, A2:A4)0.867=IF(A3>150, C3, “N/A”)
43001500=RSQ(B2:B4, A2:A4)0.867=IF(A4>150, C4, “N/A”)
• Explanation: The r-squared value is calculated only for rows with advertising spending above 150. For the first row, where the advertising spend is 100, the function returns “N/A”.

#### Example 7

• Purpose of Example: To calculate the sum of the r-squared values for predicting profit based on the number of employees.
• Data Tables and Formulas:
ABCDE
1EmployeesProfitR-SquaredResultSum
2101000=RSQ(B2:B4, A2:A4)1.000=SUM(D2:D4)
3202000=RSQ(B2:B4, A2:A4)1.000=SUM(D2:D4)
4303000=RSQ(B2:B4, A2:A4)1.000=SUM(D2:D4)
• Explanation: The SUM function calculates the total r-squared values calculated in column D. The total r-squared value is 3.000.

### Part 3: Tips and Tricks

1. Check for Errors: If the RSQ function returns an error, check if the known_y’s and known_x’s arrays have the same data points and contain numbers, not text or logical values.
2. Use with Other Functions: The RSQ function can be used with other Excel functions like INTERCEPT and SLOPE to perform more complex statistical analyses.
3. Interpret with Caution: The r-squared value measures how well the data fit the linear regression model. However, a high r-squared value does not necessarily mean the model is good, especially if the model is overfitting the data.
4. Data Scaling: If your data spans several orders of magnitude, consider scaling the data before using the RSQ function to avoid numerical errors.
5. Avoid Extrapolation: Be cautious when using the RSQ function for extrapolation. The predictions are most reliable within the range of the known_x’s.