Linear Least Squares Excel Template


by danielu13
Tags: excel, linear, squares, template
danielu13
danielu13 is offline
#1
Oct2-12, 09:58 PM
P: 68
1. The problem statement, all variables and given/known data
I had to make a Linear Least Squares Excel template for my first week's Physics lab to analyze linear data for the rest of the semester. All of my values come out correctly, except the standard deviation values. I was wondering if someone could take a look at my template and see what the problem is.


2. Relevant equations



3. The attempt at a solution
See attached file.
Attached Files
File Type: xls PY 201 Linear Least Squares Template.xls (28.5 KB, 29 views)
Phys.Org News Partner Science news on Phys.org
SensaBubble: It's a bubble, but not as we know it (w/ video)
The hemihelix: Scientists discover a new shape using rubber bands (w/ video)
Microbes provide insights into evolution of human language
Mark44
Mark44 is offline
#2
Oct2-12, 10:45 PM
Mentor
P: 21,069
Quote Quote by danielu13 View Post
1. The problem statement, all variables and given/known data
I had to make a Linear Least Squares Excel template for my first week's Physics lab to analyze linear data for the rest of the semester. All of my values come out correctly, except the standard deviation values. I was wondering if someone could take a look at my template and see what the problem is.


2. Relevant equations



3. The attempt at a solution
See attached file.
You are using D (E26) in formulas before you calculate it. Your formulas for m and b need to come after (i.e., below or to the right of) values that are used to compute them.
danielu13
danielu13 is offline
#3
Oct2-12, 10:53 PM
P: 68
Okay, I didn't realize that Excel worked in order like that. So it would work correctly if I put m and b below the D calculation, or could I put a new cell above these that is =E26, and change E26 for the new cell in the calculation formulas for m and b?

Mark44
Mark44 is offline
#4
Oct2-12, 11:32 PM
Mentor
P: 21,069

Linear Least Squares Excel Template


Quote Quote by danielu13 View Post
Okay, I didn't realize that Excel worked in order like that. So it would work correctly if I put m and b below the D calculation,
Yes.
Quote Quote by danielu13 View Post
or could I put a new cell above these that is =E26, and change E26 for the new cell in the calculation formulas for m and b?
I don't know what you're saying here. At any rate, you don't want to use a value in a formula before you have computed it. That's why you had all those #DIV/0! errors.
Ray Vickson
Ray Vickson is offline
#5
Oct2-12, 11:50 PM
HW Helper
Thanks
P: 4,676
Quote Quote by Mark44 View Post
Yes.
I don't know what you're saying here. At any rate, you don't want to use a value in a formula before you have computed it. That's why you had all those #DIV/0! errors.
Another way to prevent this is to add a small positive quantity to the denominator, so (for example) instead of dividing by some quantity D you divide by something like D + 0.000001. That will make no practical difference in almost every real case. Alternatively, you could divide by max(D, .000001); so as long as D >= 0.000001 the denominator is not changed: it is still D.

RGV


Register to reply

Related Discussions
Linear Least squares Calculus & Beyond Homework 0
Uncertainty of linear and non linear least-squares fit Set Theory, Logic, Probability, Statistics 6
Linear Least Squares Fit? General Physics 1
Numerical Methods - Linear Least Squares, Non-Linear Least Squares, Optimization Mathematics Learning Materials 0
Linear Regression, Linear Least Squares, Least Squares, Non-linear Least Squares Linear & Abstract Algebra 4