MS Excel XY Scatter graph - Linear regression trend line
Considerably Bemused Wannabe
Thread Starter
Join Date: May 2003
Location: UK
Posts: 497
Likes: 0
Received 0 Likes
on
0 Posts
MS Excel XY Scatter graph - Linear regression trend line
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.
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.
Join Date: Feb 2007
Location: Dublin, Ireland. (No, I just live here.)
Posts: 732
Likes: 0
Received 4 Likes
on
3 Posts
(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.
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.
Join Date: May 2001
Posts: 10,815
Likes: 0
Received 0 Likes
on
0 Posts
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.
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.
Join Date: Feb 2007
Location: Dublin, Ireland. (No, I just live here.)
Posts: 732
Likes: 0
Received 4 Likes
on
3 Posts
If you can't afford MATLAB, there's always Octave (which is free). There's also 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.