Differences between 'trend line' and 'correlation'?

[Donal P]

I'm using Excel 2003 under XP Professional to examine the relationship, if
any, between two sets of data - let's call them 'audit score' and 'profit'.
I have 74 data points for each set. When I graph the data points in an XY
scatterplot and then add a trendline to the data series, I get a slope for
the line of .033. However, when I use the correlation function in data
analysis, I get a .0185.

I thought these two numbers should be the same. Although the difference is
not very large, the difference gets noticeable when I look at certain subsets
of the data; on one subset, the numbers come out identical to the fourth
decimal place.

Any thoughts?

 

[jlclyde]

I'm using Excel 2003 under XP Professional to examine the relationship, if
any, between two sets of data - let's call them 'audit score' and 'profit'..  
I have 74 data points for each set.  When I graph the data points in an XY
scatterplot and then add a trendline to the data series, I get a slope for
the line of .033.  However, when I use the correlation function in data
analysis, I get a .0185.  

I thought these two numbers should be the same.  Although the differenceis
not very large, the difference gets noticeable when I look at certain subsets
of the data; on one subset, the numbers come out identical to the fourth
decimal place.

Any thoughts?

If you are trying to find the correlation between two things, you are
going the right way. Do the XY scatter plot and add the trend line
and the R squared number. This is the amount that Y is directly
affected by X. In Six Sigma, you say that there is strong correlation
when it gets to around .75. Anything lower then that, you obviously
will ahve noise or other varaibles also working to raise or lower the
Y.

Jay

 

[Bernie Deitrick]

Donal,

Make sure that you aren't forcing the trendline to a specific value at X = 0 (intercept).

Otherwise, Slope and Intercept should return the exact values of the trendline.

HTH,
Bernie
MS Excel MVP

 

[Stan Brown]

Fri, 4 Jan 2008 08:31:04 -0800 from Donal P

I'm using Excel 2003 under XP Professional to examine the relationship, if
any, between two sets of data - let's call them 'audit score' and 'profit'.
I have 74 data points for each set. When I graph the data points in an XY
scatterplot and then add a trendline to the data series, I get a slope for
the line of .033. However, when I use the correlation function in data
analysis, I get a .0185.

I thought these two numbers should be the same.

Their sign are the same, but not their values.

Overview: the correlation is how closely the points lie to a line,
and the slope is the steepness of that line.

Details:

The correlation coefficient is always between -1 and +1 inclusive.
It measures how close the points lie to the best fitting line. r = -1
means the points line up precisely on a line sloping down to the
right; r = +1 means they align precisely on a line sloping up to the
right. r = .9 means they lie in a good up-to-the-right relationship,
but not perfectly linear; r = .8 means less perfectly linear, and so
on down to r = 0, which is no linear relationship at all. Then with r
= -.1, -.2, and so on, the relationship gets closer and closer to a
straight line, but pointing down toward the right.