LINEST - accessing just R2

[JeffFinnan]

I got help the other day on using LINEST to get other values. I also see in the
Excel help how one can get just the slope and the intercept by combining with
INDEX. Is there any trick to getting just the R2? I might want evaluate in some
IF function or statement as to how good the R2 is. I will have many to do and
the x values will always stay the same.

Thanks,
Jeff

[J.E. McGimpsey]

One way:

=INDEX(LINEST(B1:B5,A1:A5,TRUE,TRUE),3,1)

Or you can use the RSQ() function instead.

In article <20040104085615...@mb-m13.aol.com>,

[JeffFinnan]

>=INDEX(LINEST(B1:B5,A1:A5,TRUE,TRUE),3,1)
>
>Or you can use the RSQ() function instead.

=================
Aha!

Thanks,
Jeff

[Jerry W. Lewis]

Substituting RSQ() assumes that a straight line is being fit even though
LINEST can do much more (probably a reasonable assumption since the OP
mentions "the slope and the intercept"

In straight line case, CORREL()^2 is better numerically than either
INDEX(LINEST(),3,1) or RSQ(), unless you have Excel 2003.

http://groups.google.com/groups?selm=3BB1DAEC.3080705%40mediaone.net

Another way these formulas are more robust than LINEST is that they work
with missing (x,y) data pairs. However, there may be problems with
formulas other than rsq=CORREL(y,x)^2, if there are missing y values
where the corresponding x is given, or visa versa.

As written the alternative formulas for slope, intercept, and steyx, and
hence seb1 and seb as well, may give the wrong answer with missing
values (like Excel 2003). SSreg and SSresid, df, and hence F and steyx
are OK with missing y values, but not with missing x values. The rest
tolerate both.

These formulas can be made to handle missing values properly (rsq
already does, so no action is required with it) if x and y in the
formulas are replaced with
IF(ISNUMBER(x)*ISNUMBER(y),x)
and
IF(ISNUMBER(x)*ISNUMBER(y),y)
and the formulas are array entered

There is one typo in the formulas.

seb = steyx/SQRT(1/COUNT(y)+AVERAGE(x)^2/DEVSQ(x))
should be
seb = steyx*SQRT(1/COUNT(y)+AVERAGE(x)^2/DEVSQ(x))

Jerry