1. ## Polynomial Regression (2000)

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

2. ## Re: Polynomial Regression (2000)

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):
<pre>=LINEST(YRange, XRange^{1,2,3},TRUE,TRUE)
</pre>

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:
<pre>{=LINEST(YRange,XRange^{1,2,3},TRUE,TRUE)}
</pre>

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:
<pre>=TRANSPOSE(LINEST(YRange,XRange^{1,2,3},TRUE, TRUE))
</pre>

3)
If you don't like array-entry (Ctrl-Shift-Enter), you can calculate each term individually, by typing formulas as follows:
<pre>=INDEX(LINEST(YRange,XRange^{1,2,3},TRUE,TRUE ),1,4)
</pre>

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!).

3. ## Re: Polynomial Regression (2000)

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:
Replace the word "description" with your descriptive text.

FAR FAR easier than polynomial regression I'm sure.

Cheers <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

4. ## Re: Polynomial Regression (2000)

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•