Results 1 to 4 of 4
Thread: Polynomial Regression (2000)

20010910, 19:17 #1
 Join Date
 Feb 2001
 Location
 Virginia Beach, Virginia, USA
 Posts
 80
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20010911, 00:23 #2
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 CtrlShiftEnter (instead of just plainold 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 CtrlShiftEnter:
<pre>=TRANSPOSE(LINEST(YRange,XRange^{1,2,3},TRUE, TRUE))
</pre>
3)
If you don't like arrayentry (CtrlShiftEnter), 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!).

20010911, 01:24 #3
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Re: Polynomial Regression (2000)
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 1Click 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=linkdescription[/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 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20010911, 12:28 #4
 Join Date
 Feb 2001
 Location
 Virginia Beach, Virginia, USA
 Posts
 80
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.