To see the strange behaviour of linest forced r2 do the following:

Paste into A1:B10

x y
0.298 0.001
1.204 0.018
2.109 0.024
3.015 0.035
3.921 0.046
4.827 0.057
5.732 0.063
6.638 0.069
7.544 0.08

Plot the data in a chart. Plot two trendlines, with equation and r2 shown,
the first not forced, the second forced through zero.

In D21:E23 enter the following unforced array formula:
=LINEST(B2:B10,A2:A10,TRUE,TRUE)

In G21:H23 enter the following forced array formula:
=LINEST(B2:B10,A2:A10,FALSE,TRUE)

To manually calculate r2 for unforced:
(note K1 is the r2 value)
K1 =1-SUM(K2:K10)/SUM(L2:L10)
K2 and copy down to K10 =(B2-E$21-D$21*A2)^2
L2 and copy down to L10 =(B2-AVERAGE(B$2:B$10))^2

To manually calculate r2 for forced (this is not accounting for df change
due to forcing)
(note M1 is the r2 value)
M1 =1-SUM(M2:M10)/SUM(N2:N10)
M2 and copy down to M10 =(B2-H$21-G$21*A2)^2
N2 and copy down to N10 =(B2-AVERAGE(B$2:B$10))^2


The m and b values of the line fit agree in both cases.
The six decimal place unforced r2 in all three cases is returned as 0.988291
The six decimal place forced r2 on the chart and manual calculation returns
0.985336, the linest returns 0.996461.

I am not sure what the propper correction for the change in df due to
forcing is or if it matter at all. The only on that I could find is as
follows: df corrected r2 = 1-(1-r2)*n/(n-1)
This correction, in M13
=1-(1-M1)*COUNT(A2:A10)/(COUNT(A2:A10)-1)
yields: 0.983503, which is not what the forced linest gives.

Thanks, I appreciate any help that you can give.

-Filtration Guy


"Jerry W. Lewis" wrote:

Post example data (within body of reply, not attachments, please).

Jerry

Filtration Guy wrote:

Thanks for your reply. I checked my version and it says that it is
"Microsoft Office Excel 2003 (11.6355.6360) SP1" "Part of Microsoft Office
Professional Edition 2003"
So maybe this problem was sort of but not quite fixed. Since posting my
original question I have found information that tells that the old error was
obvious (returned numbers not between 0 and 1), but this one seems to be off
only by a little bit.

-Filter Guy


"Jerry W. Lewis" wrote: