How do I get LINEST to show all stats?

[Guest]

I'm using Excel 2000 on a Windows 2000 system, but I've also had this problem
on other systems. When I try to get the LINEST worksheet function to display
additional linear regression statistics (by setting the fourth argument for
the function to TRUE), the only thing that shows up is the slope of the
regression line. The directions for using the function, and Microsoft Excel
Help, both say that setting this argument to TRUE will output an array of
additional regression statistics required to test for significance of the
slope, but this never happens.

Does anybody know what else might need to be done for this to work?

Thanks,
Andrew

 

[Guest]

You have to simultaneously select all the cells necessary for the output and
array enter (Ctrl-Shift-Enter) the formula to get all the results. If you
only select one cell you will only get one result (the highest order
coefficient). Similarly, if you don't array enter, you will only individual
estimates of the highest order coefficient (slope if simple linear
regression).

Jerry

 

[Mike Middleton]

Andrew -

Maybe you missed the portion of Help that says "it must be entered as an
array formula."

Select a large range of cells, type the =LINEST(...) function with
appropriate arguments, but do not press Enter. Instead, hold down Shift and
Control while you press Enter, thus "array-entering" the function. If
entered correctly, the formula bar will show curly brackets around the
function, and you'll see all of the output.

If you want a dialog box for input and output that is formatted, an
alternative is to use the Regression tool of the Analysis ToolPak (which
calls LINEST for computation).

For information about LINEST numerical inaccuracies in pre-2003 Excel, see
http://support.microsoft.com/default.aspx?scid=kb;en-us;828533

- Mike
www.mikemiddleton.com