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: Error in Y-intercept, Least-square+Excel=True?

  1. Nov 5, 2013 #1
    1. The problem statement, all variables and given/known data
    What is the error of the y-intercept and gradient of a fitted line? The line is fitted to some number of data points each with a known error in the x and y values.

    More specifically, I have fitted a linear equation to some data points using Microsoft Excel LINEST, and the error given does not seem satisfactory.

    2. Relevant equations
    As far as I know Excel uses the method of least squares.
    The specific procedure used in Excel is:
    1) Select 4 cells
    2) Use =LINEST([],[],1,1)
    3)Press shift+ctrl+enter, 4 values pop out, 1 for y-intercept, 1 for x -intercept, and a cell for an error of each the y-intercept and x-intercept

    3. The attempt at a solution
    I have fitted a linear equation to some data points using Microsoft Excel LINEST function according to specified procedure. Only the values of x,y have to be given, Exccel doesn't seem to need to know the error in the data points.

    Excel gives an 'error', but it seems to me that the given error does not depend on the errors of the data points, which seems counter intuitive. Surely if the errors on my data points were greater, the error in my gradient could be more. Thinking in pictures, if my data points had wider error bars in the y-direction, the linear line would be able to angle up and down with freedom, showing potential value the gradient can take.

    This is confusing, not sure if I can trust Excel entirely. Is it somekind of mathematical reasoning that eliminates the dependence of the calculated gradient and y-intercept from the errors in the data points?
  2. jcsd
  3. Nov 5, 2013 #2


    User Avatar
    Science Advisor
    Homework Helper
    Gold Member

    You mention uncertainty in the x values. Most stats methods assume the x values are exact, so compute regression of y wrt x.
    From the residual error in the y values, the package can estimate an error in the slope and y-intercept. (Are you sure it's giving you an x-intercept error, and not a slope error?)
    I don't know whether there's a fancier function that will allow you to specify error bars for the data. You could fudge it by turning each data point into several scattered around the actual datapoint. You would scatter the extra datapoints according to the known error range for the data.
  4. Nov 8, 2013 #3
    Ok, it appears I given an incorrect description:
    The package returns an error in slope and y-intercept in addition to the values of the slope and y-intercept. It does not give an x-intercept. Will edit the original post.

    All resources I found so far, just seem to take the error of the slope given by the package regardless of the errors in the y-values.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted