1. Limited time only! Sign up for a free 30min personal tutor trial with Chegg Tutors
    Dismiss Notice
Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Homework Help: Linear Least Squares Excel Template

  1. Oct 2, 2012 #1
    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:

  2. jcsd
  3. Oct 2, 2012 #2


    Staff: Mentor

    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.
  4. Oct 2, 2012 #3
    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?
  5. Oct 2, 2012 #4


    Staff: Mentor

    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.
  6. Oct 2, 2012 #5

    Ray Vickson

    User Avatar
    Science Advisor
    Homework Helper

    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.

Share this great discussion with others via Reddit, Google+, Twitter, or Facebook