XL2007: SLOPE vs. LINEST; how can this be?

[TomCon]

I have a formula in a cell. =SLOPE(ref1, ref2). It compultes a numeric
result with no complaint, which appears to be reasonable/valid to me.

Now, if i edit the function name ONLY and change it to LINEST (stuff inside
the () stays the same), i get #VALUE.

(The only reason i did it that way was to make absolutely my reference was
the same).

How can this be?? Anyone else ever see this? Anything to do about it? (I
need some of the "additonal regression statistics" that LINEST provides).

Thanks,
tom

 

[Mike Middleton]

tom or TomCon -

Using Excel 2007 SP1, the LINEST function works OK for me.

Select a large range of cells, type =LINEST(y-range-ref,x-range-ref,1,1) but
don't press Enter, instead after typing the closing parentheses, hold down
Ctrl and Shift while you press Enter, thus "array-entering" the function.

Press F1 and search Help for "linest function" without the quotes.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Jerry W. Lewis]

You have cell(s) that do not contain numbers (possibly empty cells) in ref1
and/or ref2. That is why LINEST returns the #VALUE! error.

SLOPE, INTERCEPT, STEYX, RSQ, CORREL, etc. will ignore non-numeric data in
ref1 and ref2; LINEST will not. This is not documented in Help for LINEST,
although it should be.

It is not necessary to array enter LINEST, provided that you only want it to
return the slope. Array entry is only required if either you want LINEST to
return more than one value, or if at least one argument to LINEST is an array
formula (such as when you ask LINEST to fit a polynomial).

Jerry