UNSOLVED
LB
louisiana bob
1 Message
0
3736
November 19th, 2003 14:00
linear regression in excel xp
There are supposedly two routes to conduct a linear regression of a x and y data set.
Route 1: Insert>Paste Function>LINEST
Route 2: Tools>Data Analysis>Regression
So, Route 2 works great, puts all the regressed data into separate cells; easily read. Route 1 sticks all the regressed data into ONE cell; cannot read anything except first data point(slope of the regressed line I think). Is there a way to be able to read the data from Route 1? If not, why has microsoft not removed this disfunctional function?
Thanks in advance.
yes, I have too much time on my hands.
JRosenfeld
2 Intern
•
4383 Posts
298
0
Posted November 19th, 2003 19:00
LINEST is an array formula. You have to select a range of cells large enough to contain the desired information, enter the function and its arguments, then press Ctrl+Shift+enter. ask help about array formula.
Example, simple linear regression of y against x. x values in column A, say A1 to A11. Corresponding values of y in columnB, B1 to B11. select cells C1:D5; type =LINEST(B1:B11,A1:A11,,TRUE); press Ctrl+shift+enter simultaneously, you'll get the regression data. look for help LINEST to see what the cell entries mean. If you choose a larger than required range of cells, the superfluous ones are filled with #N/A.