Help with trendlines

Question

Help manipulating and interpreting trendline

Applies To: Office | Office 2010 | Microsoft Office Excel | Windows 7

Hey guys,

I'm working on an assignment for uni and don't really understand how to read the data associated with the trendline (i.e. Intercept, display equation on chart, display R-squared value etc.)

For example:

Sheet 3, Chart titled - Group 1, displays R2 = -3.178

When I am writing my report, how do I reference this figure, is it 31.7% accuracy? If I untick the 'set incercept' option for that trendline, then it gives me R2 = 0.592 and I this has confused me as I don't know what this number means or what. Very confused.

We've only got till next week to complete the assignment and we only just compiled the entire data set from our pasture cuts yesterday.

Could someone help me understand this and how I would discuss the results and what context.

Cheers

Jesse

Answer 1

If you clear the "Set Intercept" check box, Excel will determine the line that best fits the data, in this case taking 1, 2, 3 as X values. "Best fit" means that the sum of the squares of the vertical distances from the line to the data points is minimized.

If you tick the "Set Intercept" check box and provide a value for the intercept, Excel will force the line to pass through the point with X = 0 and Y = intercept. This is used if you have theoretic reasons for setting a fixed intercept.

As far as I can tell, there is no reason to do so here, so you should leave "Set Intercept" cleared.

Answer 2

R² is the coefficient of determination, or the square of the correlation coefficient.

It indicates how well the trendline approximates the real data. R²=0.886 indeed is 88.6%, but as always in statistics, you have to be careful when interpreting data.

Answer 3

Thanks guys much appreciated.

Just to make sure I'm right, if the R2 = 0.00246; then I can refer to it as 0.2% accurate? If so, what if R2 = 0.886 would be 88.6% accurate?

Answer 4

In addition to Hans:

1. If you wish to add a trend line in a chart, always use a scatter plot, never a line or bar chart.
2. Don't activate the check box 'Set intercept' because there is still an Excel bug (e.g. you will receive a negative R2 value):

http://support.microsoft.com/kb/829249/

http://www.excel4managers.de/index.php?page=excel-bug-falsches-bestimmtheitsmass-im-punkt-xy-diagramm

HTH,

André