MS Excel XY Scatter graph - Linear regression trend line

[scruggs]

Hi,

I've plotted an XY scatter graph in MS excel, and plotted a linear regression trend line. How can I now calculate the difference between the data points I've entered and the linear regression line?

I'm trying to work out error between the data point and the linear regression trend line.

Thanks all.

S.

[bnt]

(from memory, assuming you're using a Linear trend)
There are 2 ways I've used before, both using the slope and intercept values of the trend:
1) Use the SLOPE and INTERCEPT functions on the data to get those values;
2) Select the trend line and open its options. You have an option to display the equation of the line, in the form y=mx+c. m is the slope, c is the intercept.

When you have the slope and intercept, you can use them to calculate the value at any point on the trend line, using y=mx+c again. Then you can subtract those values from each y data value to get the error.

[scruggs]

bnt, that's got it! Many thanks indeed for your quick and helpful reply.

S

[mad_jock]

I would go for a google search on that function.

I seem to remember that there were some dodgy stuff about the statistics functions of excel (or there used to be pre 2000). Matlab is the way forward,

A mate of mine found this out when he submitted his Phd and had to go back a redo all his statistics again. Thankfully he was working on steel so had gnats cock sized errors in the grand scale of things so didn't alter to much.

[bnt]

If you can't afford MATLAB, there's always Octave (which is free). There's also Gnumeric (http://freshmeat.net/projects/gnumeric/), a free (open source) Excel-compatible spreadsheet that claims superior numerical accuracy to Excel. You can try both and compare the accuracy if you like.