MrExcel Publishing
Your One Stop for Excel Tips & Solutions

problem with LINEST function


Posted by Reekha on February 05, 2001 10:07 AM

ok, my problem is that i don't really understand what this function does and how it works. I have given x and y values and i have to figure out y= c1x + c0 and i need to figure out c1 and co. What am i suppose to do. I looked at the help topics and read their explanation but still don't get it.


Posted by Scott R on February 05, 2001 10:40 AM

If all you want is c1 and c0, the SLOPE and INTERCEPT functions are more direct than LINEST.

Posted by Mark W. on February 05, 2001 10:50 AM

Reeka, let's assume that cells A1:B3 contains
{"X","Y";2,5.5;1,3} and you select cells D1:E1,
type LINEST(B2:B3,A2:A3)) and press Shift+Ctrl+Enter.
Cells D1:E1 will contain a single array formula,
{=LINEST(B2:B3,A2:A3))} which produces the slope (in
cell D1) and the y-intercept in cell (E1). These
are your "c1" and "c0" values respectively.

Posted by Reekha on February 05, 2001 12:43 PM

Ok: Mark
i did what you gave as an example an i got a value in D1 and #VALUE in E1. Is thsi correct? if it is than i get it. ANother thing is how do you apply this to multiple regression like when you have to figure out n5,n4,n3,n2,n1,n0....
Thanks everyone for helping me.

Posted by Mark W. on February 05, 2001 1:27 PM

Reeka, if I was a betting man -- and I am -- I'd
guess that you didn't hold down the both the Shift
and Ctrl keys when you pressed the Enter key. Select
D1:E1 and try again.

Not being a statistician I'd must recommend that you
look at the Excel Help Topic for "regression".

Posted by Mark W. on February 05, 2001 1:46 PM

Oh, now I understand what you mean when you ask about
multiple regression. Take a look at Example 3 in
the Help topic for "LINEST worksheet function.

Posted by Mark W. on February 05, 2001 1:56 PM

Reeka, I sense that you may not be all that familiar
with array formulae. Take a look at the Excel Help
Topic for "About array formulas and how to enter them".