significance of trend lines??

  • Thread starter Thread starter S Buck
  • Start date Start date
S

S Buck

Hi excel users, i have x and y paramters which produce the relationship y = 2927.4x-0.8329, i've been asked if this relationship is significant..... i dont know, how do i find this out?? I'd be really grateful for any suggestions

Cheers
 
Hi excel users, i have x and y paramters which
produce the relationship y = 2927.4x-0.8329,
i've been asked if this relationship is significant.
i dont know, how do i find this out?
Click to expand...

I presume that by "significant", you mean goodness of fit
(correlation).

Use R-squared. The closer to 1, the better the fit.

If you are using worksheet functions to "see" that trendline equation,
use the RSQ() function.

If you are see that trendline equation in a chart, right-click on the
trendline and click on Format Trendline; or right-click a data point
or curve/line and click on Add Trendline. In either case, click the
Options tab and select Display R-squared.
 
I presume that by "significant", you mean goodness of fit
(correlation).

Use R-squared. The closer to 1, the better the fit.
Click to expand...

... sort of. Actually, I think it is something like ... 'the closer to 1,
the smaller the residual' (errors left after fitting) ... but you need to
be very careful assuming a "good fit" from that. Gentle curves may have r
squareds close to 1 ... but still not fit the data all that well (for
example). Similarly, you can fit a 'good line' through 2 disconnected groups
of data and get similarly misled. :)
Presumably, these data have been graphed ... but this typing is just in case
they haven't. :)
 
You can test whether the regression explains more than a simple mean by doing
a t-test on the estimated slope. Divide the estimated slope by its standard
error to get t and check that the value of TDIST(ABS(t),df) is appropriately
small (often p<0.05). Lack of significance does not mean that the
relationship is not real, instead, it might indicate that you simply do not
have enough data, given the data variability.

For example, with the data set given in Help for the SLOPE function,

=TDIST(ABS(INDEX(LINEST(ydata,xdata,,TRUE),1,1)/INDEX(LINEST(ydata,xdata,,TRUE),2,1)),INDEX(LINEST(ydata,xdata,,TRUE),4,2),2)

returns 0.60 indicating that the linear regression slope is not
statistically significant. A plot of the data suggests a curved relationship
with multiple y values for a single x value (possibly due to additional
predicter variables that have not been supplied) rather than a straight line.

Beware of using R-squared alone for comparing alternate models, as that may
lead to over-fitting your data
http://en.wikipedia.org/wiki/R-squared#Adjusted_R2

Jerry
 
Back
Top