Share via

what does the LINEST standard error represent and how does it apply to real life?

Anonymous
2013-08-04T10:41:21+00:00

With reference to the LINEST function, what does the standard error in the slope and intercept represent ?  How does it apply to 'real life'? If I measure a sample against this regression line to obtain the predicted value, and I need to report it with uncertainty limits, do I use the LINEST standard errors or use the RMS or %Rel RMS (=(RMS/mean of X)*100))

I used the LINEST function to return regression coefficients on a best fit line but when I plotted the -ve and +ve standard error curves against the best fit line, I found that the error curves where well in excess of the RMS ( SQRT(SUM(residuals(Xa-Xc) squared)/ df) ) for the data points. In other words (always difficult to explain without a diagram), all the actual data points fit VERY comfortably within the +/- standard error curves

I hope the above makes sense - but pls ask for clarification if you need it

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-08-04T20:47:03+00:00

    Scoobby wrote:

    With reference to the LINEST function, what does the standard error in the slope and intercept represent?  How does it apply to 'real life'? 

    Take a look at http://en.wikipedia.org/wiki/Simple_linear_regression, especially the sections "Confidence Intervals", "Normality Assumption" and "Numerical Example".

    For "large n", it means we are about 68.3% confident that the actual slope, for example, is between the estimated slope plus-or-minus the standard error of the slope.  More generally, +/-stdErr*NORMSINV((1-pctConf)/2)

    In practice, we should use the Student's-t distribution instead of the normal distribution:  +/-stdErr*TINV(1-pctConf,df), where df=n-2.

    Scoobby wrote:

    If I measure a sample against this regression line to obtain the predicted value, and I need to report it with uncertainty limits, do I use the LINEST standard errors

    I would use the RSQ value.  It does not translate into confidence limits per se.  But it is a measure of fit.

    Take a look at http://en.wikipedia.org/wiki/Coefficient_of_determination, notably the section "Interpretation" and the sections starting with "Relation to Unexplained Variance".

    Scoobby wrote:

    I used the LINEST function to return regression coefficients on a best fit line but when I plotted the -ve and +ve standard error curves against the best fit line, I found that [...] (always difficult to explain without a diagram) all the actual data points fit VERY comfortably within the +/- standard error curves

    First, you might look at the formula under "the confidence band has hyperbolic form given by the equation" in the aforementioned "Simple Linear Regression" wikipage.

    Second, to show us your work ("explain [with] a diagram"), you can post the URL of an example Excel file that you uploaded to a file-sharing website.  The following is a list of some free file-sharing websites; or use your own.

    Box.Net: http://www.box.net/files

    Windows Live Skydrive: http://skydrive.live.com

    MediaFire: http://www.mediafire.com

    FileFactory: http://www.filefactory.com

    FileSavr: http://www.filesavr.com

    RapidShare: http://www.rapidshare.com

    You can also use this forum's new Add Image feature on the toolbar of the reply-box to paste an image into your posting.  It does require that you save a local JPG file (preferably) first.  But an Excel file is preferable.

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2013-08-04T17:50:30+00:00

    Thanks for the 2 links which werent really specific to my query. I have already done a google search on this subject but didnt find a suitable explanation. Maybe it was the search terms I used?

    I will try again using the string you suggest.

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2013-08-04T13:15:12+00:00

    I suggest you do an internet search using "Regression standard error slope"

    Here is one hit http://stattrek.com/regression/slope-test.aspx

    This page might also help

    http://calcscience.uwe.ac.uk/w2/sg/sg_LinRegress/sg_LinRegress.htm

    You should also think about the standard error in the predicted y values; in a 5 rows by 2 columns LINEST output this is in column 2 of row 3 and may also be found with the STEXY function

    best wishes

    2 people found this answer helpful.
    0 comments