**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: Examples**

Example 1

**Purpose of Example**: Calculate the r-squared value for sales prediction based on advertising spend.**Data Tables and Formulas**:

A | B | C | D | |
---|---|---|---|---|

1 | Advertising | Sales | R-Squared | Result |

2 | 100 | 500 | =RSQ(B2:B4, A2:A4) | 0.867 |

3 | 200 | 1000 | ||

4 | 300 | 1500 |

**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**:

A | B | C | D | |
---|---|---|---|---|

1 | Employees | Profit | R-Squared | Result |

2 | 10 | 1000 | =RSQ(B2:B4, A2:A4) | 1.000 |

3 | 20 | 2000 | ||

4 | 30 | 3000 |

**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**:

A | B | C | D | |
---|---|---|---|---|

1 | Units Sold | Revenue | R-Squared | Result |

2 | 50 | 5000 | =RSQ(B2:B4, A2:A4) | 1.000 |

3 | 100 | 10000 | ||

4 | 150 | 15000 |

**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**:

A | B | C | D | |
---|---|---|---|---|

1 | Support Staff | Satisfaction | R-Squared | Result |

2 | 5 | 70 | =RSQ(B2:B4, A2:A4) | 1.000 |

3 | 10 | 80 | ||

4 | 15 | 90 |

**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**:

A | B | C | D | |
---|---|---|---|---|

1 | Blog Posts | Traffic | R-Squared | Result |

2 | 10 | 1000 | =RSQ(B2:B4, A2:A4) | 1.000 |

3 | 20 | 2000 | ||

4 | 30 | 3000 |

**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**:

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Advertising | Sales | R-Squared | Result | Message |

2 | 100 | 500 | =RSQ(B2:B4, A2:A4) | 0.867 | =IF(A2>150, C2, “N/A”) |

3 | 200 | 1000 | =RSQ(B2:B4, A2:A4) | 0.867 | =IF(A3>150, C3, “N/A”) |

4 | 300 | 1500 | =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**:

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Employees | Profit | R-Squared | Result | Sum |

2 | 10 | 1000 | =RSQ(B2:B4, A2:A4) | 1.000 | =SUM(D2:D4) |

3 | 20 | 2000 | =RSQ(B2:B4, A2:A4) | 1.000 | =SUM(D2:D4) |

4 | 30 | 3000 | =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**

**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.**Use with Other Functions**: The RSQ function can be used with other Excel functions like INTERCEPT and SLOPE to perform more complex statistical analyses.**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.**Data Scaling**: If your data spans several orders of magnitude, consider scaling the data before using the RSQ function to avoid numerical errors.**Avoid Extrapolation**: Be cautious when using the RSQ function for extrapolation. The predictions are most reliable within the range of the**known_x’s**.