Linear regression problem in Excel

[Brian]

I am trying to perform a linear regression analysis on 2 columns of data.

I'm using the LINEST function under statistical functions.

The problem I'm having is with the result I obtain from LINEST.

Using the formula Y=Mx + b (default trendline formula) I obtain the value
for M but the constant b is not being factored in. I have not entered
anything in the Const field thereby allowing Excel to calculate the field
itself.

When I chart the data and apply the linear regression trendline and select
"display equation on chart" I obtain y = 0.0284x + 31.584 which is the
correct formula. Doing it with LINEST I only get .0284 as the result.

I imagine I'm doing something wrong but what it is I don't know!

Thank you for any help.

 

[Bernard Liengme]

Said very kindly: you are asking the wrong people. Excel questions should be
sent to one of the Excel newsgroups; e.g. msnews's
microsoft.public.excel.worksheetfunctions.

Assuming you have selected two side-by-side cells and entered formula such
as =LINEST(B2:B21,A2:A21); have you remembered to completed this using
SHIFT+CTRL+ENTER not just ENTER? This is necessary because LINEST is an
"array function" - see www.cpearson.com for more on that topic.

If you select a block 2 by 5 you also get the stats associated with the
regression (R-squared, etc)

If you are not using the stats, and the regression is linear, why not use
the simple SLOPE and INTERCEPT functions?

 

[Brian]

Bernard,

Thank you very much for the reply. I was not using SHIFT+CTRL+ENTER!

I will be certain to enter Excel questions in the appropriate newsgroup in
the future. Right after you mentioned it I looked in the list of newsgroups
and found the Excel ones!

Thanks again.

Brian