Rafiul Haq
We’re going to show you 2 methods of how to find Residual Standard Error in Excel. To demonstrate our methods, we’ve picked a dataset with 3 columns: “Company”, “Advertising”, and “Revenue”. Moreover, we’re finding the relationship between Advertising and Revenue generation of Company XYZ. Consequently, this error will tell us how much our predicted values differ from the actual values.
Download Practice Workbook
2 Ways to Find Residual Standard Error in Excel
1. Regression Analysis to Find Residual Standard Error
For the first method, we will find the Residual Standard Error by applying the Regression Analysis to our dataset. Firstly, we’ll need to enable the Data Analysis feature in Excel.
Steps:
- Firstly, press ALT, F then O to bring up the Excel Options window.
- Secondly, from Add-ins >>> select “Go…”.
Another dialog box will appear.
- Thirdly, select “Analysis Toolpak” and press OK.
Thus, we will enable the Data Analysis feature. We can see the newly created feature inside the Data tab.
- Then, click on Data Analysis.
The Data Analysis dialog box will appear.
- After that, select Regression and press OK.
Now, we’ll select the following options inside the Regression dialog box –
- Input Y Range: D4:D10. Here Y Range means Dependent variable (Revenue).
- Input X Range: C4:C10. Here, X Range means Independent variable (Advertising).
- Select Labels.
- Output Range: B12. We’ll put the Regression value here.
- Finally, press OK.
From the Summary Output, we’ll get our value. The Standard Error 74.30977729.
We can get it if we Square Root the value of the Mean Square of Residual (5521.94).
Read More: How to Calculate Standard Error of Regression in Excel (with Easy Steps)
2. Find Residual Standard Error Using Generic Formula
We’ll find Residual Standard Error using this formula Sqrt((y-ŷ)^2/df).
Where
- df = degrees of freedom.
- Residual = Observed value (y) – Predicted (advertising) value (ŷ).
In this last method, we’re going to use a formula to find Residual Standard Error in Excel. Here, we’ll use the LINEST function to find the Linear Regression equation then using the above formula we’ll find our values. We’ve also used SUM, SQRT functions in this section to get our result. Moreover, we’ve changed our dataset.
Steps:
Now, we’re going to find the Intercept and Slope values of our Regression model.
- Firstly, we’ll select the cell range G9:H9 and type the following formula.
=LINEST(C6:C11,B6:B11)This function returns statistical values from provided X and Y values. Our y_values are in cell range C6:C11 and the x_values are in cell range B6:B11.
- Secondly, press CTRL + ENTER.
This will convert our formula to an array formula, due to this we’ll see a curly bracket.
We’ll find the Intercept as 14.46445118 and slope -10.58989665. Our Regression equation will be ŷ= -10.59 + 14.46x.
Now, we’ll find the Predicted Revenues.
- Firstly, select the cell range D6:D11.
- Secondly, type the following formula.
=$H$9+$G$9*B6We’re using this formula that we got the Regression equation.
- Then, press CTRL + ENTER.
We’ll get the Predicted values from this.
Then, we’ll find the squared difference between predicted and actual values.
- Select the cell range E6:E11 and type this formula.
=(C6-D6)^2
- After that, press CTRL + ENTER.
- Type this formula in cell E13.
=SUM(E6:E11)We’re adding all the values from cell E6 to E11.
Here, df means degrees of freedom. We have 6 values in our dataset and there are 2 parameters in our dataset. Hence our df = 6-2 = 4.
Then, we’ll find the value of Residual Standard Error.
- Type this formula in cell E15.
=SQRT(E13/E14)We’re finding the square root value of the cell of the value E13 divided by the value of E14.
- Finally, press ENTER.
Thus, we’ll get our value.
Read More: How to Calculate Standard Error of Regression Slope in Excel
Practice Section
We’ve added a practice dataset in the Excel file.
Conclusion
We’ve shown you 2 methods of how to find Residual Standard Error in Excel. Additionally, if you have any questions, feel free to comment below. Thanks for reading, keep excelling!
