LINEST Function

What version of Excel. Doesn't seem to do much for me in Excel for Mac 2008.

Widely ignored feature:



Yvan KOENIG (from FRANCE vendredi 27 février 2009 22:07:05)

I think he is trying to indicate that you can add a trendline to a chart in Number '09 without having to calculate it within a table first.

I believe you are looking for the INDEX function in addition to LINEST. It's a bit too long to explain it all here. If you open the function browser (use the Fx icon on the toolbar or go to the menu item Insert/Function/Show Function Browser) and type in LINEST it'll show you how to get what you need. There is a link in the description for "additional stats" which includes errors and the like. Post back here if it isn't clear enough and you need more help.

I don't know to which poster you are responding.
If it's to me, maybe you missed that we may change the parameter: Order in the Inspector 😉

If it's not to me, it seems that you missed the pages 249 … 252 of the iWork Formulas and Functions User Guide.
Alas you aren't the first one 😟

Yvan KOENIG (from FRANCE vendredi 27 février 2009 23:08:57)

Sure can (I think). This seems to work but I'm not 100% sure it's kosher. At first blush it seems like it should throw off the least squares fit but it doesn't. Before I start, though, if you are only interested in the intercept as a number and not making the trendline extend on the chart, calculate it with INTERCEPT. But if you're interested in the chart, read on.

B and D columns are the X axis seconds starting at 0 and going past 60 to whatever the highest number is. Same numbers in each column

C column is the first set of Y data. Leave blank the rows with seconds greater than 60

E column is the second set of Y data, leave blank the rows for seconds less than 60

In column E at 0 seconds, put the INTERCEPT function. Select only the range over which you have data, don't include the rows with seconds less than 60.

It extends the trendline but it also puts a data point at the intercept.

At second blush, it won't throw off the least squares fit. That 0-second datapoint is right in line with the trendline that would have been created if it wasn't there so it has no effect.

Also, in case it wasn't clear, when I said to "Select only the range over which you have data" for the INTERCEPT function, I meant the data for the 2nd set of Y's.

And, of course, the last step is to create the chart, selecting all four columns, from 0 seconds all the way to the end.

I'm going to have to start posting images, they're worth 1000 words.