R-squared, also known as the coefficient of determination, is a statistical measure that determines the proportion of variance in the dependent variable that can be predicted from the independent variables in a linear regression model. It is widely used in various fields and industries to understand the strength of relationships between variables. In this article, we will discuss how to calculate R-squared in Excel using its built-in functions and tools.

Step 1: Organize your data

For this example, we’ll use four data points for both x (independent variable) and y (dependent variable):

x: {3,4,5,6}

y: {2,3,4,5}

In Excel, enter these values in two columns (A and B). It should look like this:

| A | B |

|—|—-|

| 3 | 2 |

| 4 | 3 |

| 5 | 4 |

| 6 | 5 |

Step 2: Compute the slope and intercept

In Excel, the SLOPE and INTERCEPT functions are used to calculate the slope and intercept of a linear regression model. Type the following formulas into cells C1 and C2:

C1: `=SLOPE(B1:B4,A1:A4)`

C2: `=INTERCEPT(B1:B4,A1:A4)`

The slope (m) should be 1, and the intercept (b) should be -1.

Step 3: Calculate predicted values (ŷ)

Using the slope and intercept values obtained in Step 2, predict y-values using the formula ŷ = mx + b. In Excel:

Column D:

D1: `=C1 * A1 + C2`

D2: `=C1 * A2 + C2`

D3: `=C1 * A3 + C2`

D4: `=C1 * A4 + C2`

Step 4: Calculate the residuals (y – ŷ)

The residuals represent the differences between the actual and predicted values. In Excel, calculate the residuals in Column E:

E1: `=B1-D1`

E2: `=B2-D2`

E3: `=B3-D3`

E4: `=B4-D4`

Step 5: Calculate the sum of squared residuals (SSR) and total sum of squares (SST)

SSR represents how well your model fits the data, while SST shows the variation within your data. In Excel:

F1: `=SUMXMY2(B1:B4,D1:D4)` – to calculate SSR

F2: `=DEVSQ(B1:B4)` – to calculate SST

Step 6: Calculate R-squared

Finally, divide SSR by SST and subtract that value from 1 to obtain R-squared. In Excel:

G1: `=1 – (F1/F2)`

Your R-squared value should be equal to 1, indicating a perfect linear relationship between the variables x and y.

Conclusion

Calculating R-squared in Excel is a straightforward process that helps you understand how well your regression model fits your data. By following these steps, you can easily assess and interpret relationships between variables for various applications.