Go Back  PPRuNe Forums > Misc. Forums > Computer/Internet Issues & Troubleshooting
Reload this Page >

MS Excel XY Scatter graph - Linear regression trend line

Computer/Internet Issues & Troubleshooting Anyone with questions about the terribly complex world of computers or the internet should try here. NOT FOR REPORTING ISSUES WITH PPRuNe FORUMS! Please use the subforum "PPRuNe Problems or Queries."

MS Excel XY Scatter graph - Linear regression trend line

Old 12th Dec 2009, 19:07
  #1 (permalink)  
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.
scruggs is offline  
Old 12th Dec 2009, 20:36
  #2 (permalink)  
bnt
 
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.
bnt is offline  
Old 12th Dec 2009, 21:55
  #3 (permalink)  
Considerably Bemused Wannabe
Thread Starter
 
Join Date: May 2003
Location: UK
Posts: 497
Likes: 0
Received 0 Likes on 0 Posts
bnt, that's got it! Many thanks indeed for your quick and helpful reply.

S
scruggs is offline  
Old 13th Dec 2009, 06:55
  #4 (permalink)  
 
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.
mad_jock is offline  
Old 13th Dec 2009, 20:10
  #5 (permalink)  
bnt
 
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.
bnt is offline  

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Contact Us - Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service

Copyright © 2024 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.