Linest: return intercept only

[Tim89]

Hey guys, I'm using the Linest function, looking something like this: =LINEST(B98:B133,NQ98:NT133,TRUE). So, excel performs a straight-line regression, with known y's in B98:B133 and known y's in NQ98:NT133. Note that I include an intercept (aka constant or alpha), hence the 'TRUE', and that I have 4 variables (in column NQ, NR, NS and NT). I press CTRL + SHIFT + ENTER, and presto, in the first four cells my regression factors are returned, and my intercept is returned in the fifth cell. So far so good.

Problem is, I'm only interested in the intercept, thus, the fifth cell. Just ignore the first four you might say, but I've got a huge matrix of data, so I want to autofill this formula both down as right. Especially autofilling to the right will give me problems. So in my formula bar, I need to add something so that only my fifth value of the array is returned. An alternative is a formula that "mirrors" my array, so that the fifth return becomes the first, the fourth becomes the second etc.

Anyone any thoughts on this? Thanks!

[oeldere]

Although I am not familiair with the formula linest,


1. It would help to see the workbook.

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Crooza]

If you're not interested in the first four cells - the coefficients - then you can just use the intercept function to get the fifth cell only.

It's in the form of = intercept(known y, known x)

[Tim89]

Hi Oelderen, thanks for your reply, but I think I found the answer myself already . Adding an index function allows me to choose which cell in my array I want to have returned. So, in the example of my opening post, this would be =INDEX(LINEST(B98:B133,NQ98:NT133,TRUE),1,5).

So, linest returns an array with 1 row and 5 columns, of which I am only interested in the fifth column (in my case you can replace the word column by cell). That is what the index function does: returning row 1, column 5. Sorted!

Sorry for posting, but hopefully this posts helps other people in the future.

[Tim89]

Also Crooza thanks, but the intercept function only works with 1 independent variable. If you have more, like I do (four), then intercept doesn't work...

[Crooza]

Also Crooza thanks, but the intercept function only works with 1 independent variable. If you have more, like I do (four), then intercept doesn't work...

Got it. And yes your method of using the index to get the 5th value should work nicely.