How to Find Residual Standard Error in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

find residual standard error in excel


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.

Regression Analysis: how to find residual standard error in excel

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.

Regression Analysis to find residual standard error

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).

how to find residual standard error in excel

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.

Using formula to find residual standard error

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.

Using formula to find residual standard error

Now, we’ll find the Predicted Revenues.

  • Firstly, select the cell range D6:D11.
  • Secondly, type the following formula.
=$H$9+$G$9*B6

We’re using this formula that we got the Regression equation.

  • Then, press CTRL + ENTER.

We’ll get the Predicted values from this.

Finding predicted values to find residual standard error

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.

Finding difference between predicted and actual values to find residual standard error

  • 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.

Formula to find Residual Standard Error

  • Finally, press ENTER.

Thus, we’ll get our value.

How to Find Residual Standard Error in Excel

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!


Related Articles

Rafiul Haq
Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo