Linear Regression Error on Excel

[Calabi_Yau]

This is for an experimental physics homework,I am using the latest version of MS Excel. I have a set of data, I perform linear regression on them and it gives me a line y=ax + b.

Given that both a and b have physical significance I would like to know how could I know the uncertainty associated with both a and b, so I could get meaningful results.

How do I do this? The statistics pack seems to give everything but these values.

Any help would be appreciated.

 

[DrClaude]

They are easy to calculate "by hand", by which I mean by setting up the proper equations in the spreadsheet yourself. See http://mathworld.wolfram.com/LeastSquaresFitting.html for details (the equations for the standard errors are the last ones on the page).

 

[Yanick]

Uncertainty with regards to coefficients of a model fit to data can be inferred from so called "Goodness of Fit" measures. There are many different ways to approach this however, since you are using Excel, the easiest for way for you is to have Excel display the R² value. You can do this by either right clicking on your line, selecting Format Trendline and checking off the box next to "Display R-squared value on chart." If you are fitting the line then the same dialog box will have that option on the bottom. Interpretation of the value is fairly straight-forward, a quick Google search will gives lots of discussions.

 

[saveGuard]

useful textbook

There is a useful textbook explaining things in an introductory manner. Have a look at: "Data Fitting and Uncertainty (A practical introduction to weighted least squares and beyond)", ISBN 978-3-8348-1022-9.

 

[LudusRex]

As a scientist, it is important to accurately determine the uncertainty associated with your data in order to obtain meaningful results. In Excel, the uncertainty for a and b in linear regression can be calculated using the LINEST function. This function returns an array of values, with the first value being the slope (a) and the second value being the y-intercept (b). The third and fourth values in the array are the uncertainties for a and b, respectively.

To use the LINEST function, you will need to select the cells containing your data and the corresponding y-values. Then, in an empty cell, type "=LINEST(y-values, x-values, TRUE, TRUE)". This will return an array of values, with the third and fourth values being the uncertainties for a and b.

Additionally, you can also use the Analysis ToolPak in Excel to perform a regression analysis and obtain the uncertainties for a and b. To do this, go to the "Data" tab and click on "Data Analysis". Then, select "Regression" from the list of analysis tools and input your data. The resulting output will include the uncertainties for a and b.

It is important to note that the uncertainties calculated using these methods assume that the data follows a linear relationship and that there are no systematic errors present. If you suspect there may be systematic errors in your data, it is recommended to consult with your instructor or a statistician for further guidance on how to accurately determine the uncertainties for a and b.