Linear Regression

[al boccia]

I have been struggling to get the LINEST function to work for me. I have
checked out the instructions for the function, and have followed them
carefully, but I can't get LINEST to return either the basic results (slope
and intercept) or the full array of output (with the STATS option set to TRUE
(or 1). I am entering the formula as an array formala, as instructed. No
matter what I do, all I get is the slope result as a single number.

Any suggestions would be greatly appreciated.

Al Boccia

 

[Dana DeLouis]

all I get is the slope result as a single number.

HI. Sounds like you are entering your function into 1 cell.
Select a few cells (rows & columns) and then Array enter your equation.

You should see different information in each cell.
= = =
HTH
Dana DeLouis

 

[Chip Pearson]

Not only do you need to enter the function as an array formula, you
must enter it in a range of cells, not a single cell. Since the
function returns an array of values, it must be entered into a range
big enough to display all the return values. E.g., select range A1:C5,
type the formula and press CTRL SHIFT ENTER.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Thu, 11 Dec 2008 14:11:02 -0800, al boccia <al

 

[al boccia]

Thanks Chip and Dana. I have been selecting a range of cells large enough to
hold the results array before specifying the formula. No luck. I have pretty
much given up on using EXCEL for this task. Going to put my data in SPSS,
which is going to be painful but its all I can think of at this point.

Any other thoughts most welcome.

Al Boccia

 

[Mike Middleton]

Al Boccia -

If you have a single X variable, you could use worksheet functions
INTERCEPT, SLOPE, RSQ, and STEYX. But if you need other diagnostics or if
you have multiple X variables, you'll have to figure out how to use LINEST
or use statistical software.

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

 

[al boccia]

Thanks Mike -

Maybe you can clarify two things for me.

1. Do I need the statistical function add-in to get LINEST to work? All the
individual functions (SLOPE, STEYX, etc, are working presently)

2. What I really need from the analysis is the standard error of the
regression coefficient. Since I can get STEYX to work, and since I have only
one independent variable (that is one x argument), is the standard error
returned by STEYX the same as the standard error of the coefficient? If so
I'm home free.

Al Boccia

 

[David Biddulph]

Excel help for the LINEST function tells you where to find the various
standard error terms, and help for the STEYX function tells you what it
produces. It's the standard error for the y estimate, not for the
coefficient. You'll see that you get the same result as for the sey term
from LINEST.

 

[Jerry W. Lewis]

It sounds as though you have not understood the instructions for array entry
of a formula that returns multiple values.

Select cells A1:B5, and with these 10 cells still selected (highlighted)
click on the formula bar and type
=LINEST({3100,4500,4400,5400,7500,8100},,,TRUE)
With these 10 cells still selected (highlighted) and the cursor in the
formula bar with this formula showing, hold down the Ctrl and Shift keys
while you press enter.

If you did it right, the 10 cells will still be selected (highlighted) and
the formula bar will show the formula
{=LINEST({3100,4500,4400,5400,7500,8100},,,TRUE)}
Note the surrounding curly brackets that you did not type. Excel supplies
them to indicate that the formula has been array entered. (If you click in
the formula bar, the surrounding curly brackets will disappear.)

If you have done all of this correctly, Cells A1:B5 will display the values
1000 2000
133.0950251 518.3306538
0.933831377 556.7764363
56.4516129 4
17500000 1240000
which have the interpretation documented in Help for LINEST.

If you have done this successfully, then you shold be able to see what part
of it you did not do correctly for your original question.

Jerry

 

[Mike Middleton]

Al Boccia -

Do I need the statistical function add-in to get LINEST to work? <
No.

... is the standard error returned by STEYX the same as the standard error
of the coefficient? <

No.

If you prefer to not use the array-entered LINEST function, you can get the
standard error of the regression coefficient this way:

=STEYX(known_y's,known_x's)/(STDEV(known_x's)*(COUNT(known_x's)-1)^0.5)

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