Polynomial Regression (2000)

Topic

New Reply

Is there a way to get the data from a trendline into a cell? I have a 3rd order polynomial regression that I want to get the coeffiecents from.
Thanks

Reply | Quote

Replies

Here are several ways, which are all variations on the same theme. This is a distillation of what I learned in many exchanges with Hans and others (many thanks!)

1)
Select four cells together in a single row. Type the following formula, then type Ctrl-Shift-Enter (instead of just plain-old Enter):

=LINEST(YRange, XRange^{1,2,3},TRUE,TRUE)

where YRange is the range containing the Y data on the chart, and XRange contains the X data on the chart. Make sure YRange and XRange have the same number of cells in them, and that none of the cells are empty. The formula in the formula bar will now look like this:

{=LINEST(YRange,XRange^{1,2,3},TRUE,TRUE)}

The first cell will contain the cubed term, second cell the squared term, third cell the X term and the fourth cell the constant term.

2)
If you don’t like the terms in a row and want them in a column instead, select four cells in a column and type the following formula, then press Ctrl-Shift-Enter:

=TRANSPOSE(LINEST(YRange,XRange^{1,2,3},TRUE,TRUE))

3)
If you don’t like array-entry (Ctrl-Shift-Enter), you can calculate each term individually, by typing formulas as follows:

=INDEX(LINEST(YRange,XRange^{1,2,3},TRUE,TRUE),1,4)

This particular one will calculate the constant term only. For the other tterms, use the same formula, but change the 4 to 3, 2 or 1 for the X term, squared term and cubed term respectively.

If your data has gaps at the “ends”, these formulas will choke on the empty cells. Someone else posted a very nice custom VBA formula that takes account of empty cells, but I don’t know how to post a link to another thread (sorry, maybe someone else can point the way!).

Reply | Quote

I’m going to stay far away from polynomial regression – but I will answer the question about linking to another thread.
The button circled in Red is from the 1-Click TagPanel. It will insert a tag (hard brackets have been replace by | symbol, so the board doesn’t turn it into code)like this:
|url=link|description|[/url|
Replace the word “link” with the address of the thread you wish to link to (this can be copied out of the address bar).
Replace the word “description” with your descriptive text.

FAR FAR easier than polynomial regression I’m sure.

Cheers

Reply | Quote

Thanks.
That is what I was looking for. I was try to avoid making multiple x, x^2,… columns. Didn’t think to use the XRange^{1,2,3}. Also wanted a 0 intercept which is false on the 3rd parameter. I just love double negatives.

Reply | Quote