R2 value inconsistent between graph and linest function

[LML]

For a data correlation, I have added a trendline to the plotted data with R2, and also used the linest function on the same data.

The two R2 values match when there is an intercept, but when the correlation is forced through the origin the R2 values differ by up to 2%.

Any ideas why this is? I'm inclined to believe the graph R2 as this is slightly lower when forced through the origin, whereas the linest R2 is higher.

Cheers

 

[mirabeau]

Louise,

You may find the thread
http://www.mrexcel.com/forum/showthread.php?t=364688
or
http://support.microsoft.com/kb/829249
of some relevance to your problem. If you're using Excel 2003.

Otherwise, regressions are pretty easy things to calculate. Although they can be somewhat tedious manually. I don't like to think of you doing them manually.

 

[mirabeau]

Louise,

In case you want to recheck, or ever do regressions etc again, you can use this VBA code if you'd like an alternative to LinEst.

Just put your dependent variable data in columnA (with no header), and your independent (or regressor) variables in columns B and C and ... (however many you want to use).

Then just run the code.

Sub regression()
Dim a As Range, n As Long, m As Long
Dim y, x, gg
Dim coeff, Rsq As Single

Set a = Range("A1").CurrentRegion
n = a.Rows.Count: m = a.Columns.Count
Cells(1, m + 1).Resize(n) = 1
y = a.Resize(, 1)
x = a.Resize(, m).Offset(, 1)
Cells(1, m + 1).Resize(n).ClearContents

With Application
    gg = .MInverse(.MMult(.Transpose(x), x))
    coeff = .MMult(gg, .MMult(.Transpose(x), y))
    Rsq = Evaluate(.MMult(.Transpose(y), .MMult(x, coeff))) / .SumSq(y)
End With

'Outputs follow
Cells(1, m + 3) = "Coeffs"
Cells(2, m + 3).Resize(m) = coeff
Cells(1, m + 4) = "RSq"
Cells(2, m + 4) = Rsq
End Sub