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.