Results 1 to 15 of 15
Thread: 4th order poly fit

20010404, 18:56 #1
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
4th order poly fit
Hi everybody!
On a Chart, you can add a trendline using 4th order polynomial fit, display the equation, then (manually) transfer those numbers to a spreadsheet for functionsolving, etc.
I wrote a VBA routine that performs this automatically. Someone asked me if there is some other, notquitesoseverelymanual method that can be used, so he doesn't have to adapt my code. Is there a way with the Analysis Toolpack? If so, how? I looked at the regression tool, but it doesn't seem to be able to do anything higher than 2nd order. Am I missing something? Is there some other way?
Thanks!

20010405, 10:40 #2
 Join Date
 Dec 2000
 Posts
 140
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
Try the "Polynomial Regression using LINEST", this trick is good for all degrees:
<A target="_blank" HREF=http://www.stfx.ca/people/bliengme/exceltips.htm>http://www.stfx.ca/people/bliengme/exceltips.htm</A>
Remark: If your list separator is a semicolon ";", replace _first_ comma of the formula by a semicolon

20010405, 11:37 #3
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
You can do it with the analysis toolpak. In fact, fitting a polynomial of the nth degree is nothing more than multilinear regression where the variables are the powers of x.
If you have X and Y values, then you need to add a column for each power of X you want to add to your model. e.g. if you want to fit a 4th order polynomial, just add a column next to the column with the Xdata, with X^2, one with X^3 and one with X^4. Then choose Tools >> Data Analysis, select the Range for the Yvaraible and select the Range, including all columns with Xdata for the Xinput. When clicking OK the output gives the regression coefficients and the regression statistics for the polynomial fit.
Watch out if you fit through the origin, the analysis toolpak statistics is not correct for the adjusted Rsquare.
You can use the Excel function LINEST, but you'll need to prepare the Xdata in the same way as described above.

20010405, 16:54 #4
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
cri,
Thanks a ton! I wish I had this info about two years ago. Also thanks to Hans for responding about the Analysis Toolpack. Since the LINEST technique is so easy, I will use that instead.

20010406, 04:01 #5
 Join Date
 Dec 2000
 Posts
 140
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
JIMbythebay, glad to help. However, this method has the following drawbacks:
 The coefficients are returned in a row, which is a nuissance if you have a several curves in table form. In this case, as you can not move them separately, link them to other cells and then hide the row.
 If you must insert a column in the used area afterwards, you can not, unless you repeat the process.

20010406, 18:41 #6
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
True indeed. For the application I would have used it in two years ago, each data series was in a column (of which there were potentially 70), and I put the coefficients (which I extracted from the text box of the trend line equation) in five rows below each column. That is not possible using the array entry technique, so I would have had to arrange the data one series per row instead, or done some weird transposing.

20010406, 18:49 #7
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
You can also use the INDEX(LINEST(..), i, j) where i and j are integers, to extract each individual element from the output. So, you can put the coefficients whereever you want.

20010618, 20:57 #8
 Join Date
 Dec 2000
 Posts
 140
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
BTW: If you use TRANSPOSE, you will get the coefficients in column form:
=TRANSPOSE(LINEST(etc. etc))
Do not forget to close with Ctrl+Shift+Enter

20010618, 23:18 #9
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
cri,
You are right! Thanks very much, that is the final piece to a puzzle I have been puzzling over for about 3 years now!

20010623, 05:58 #10
 Join Date
 Dec 2000
 Posts
 140
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
Do you happen to know how to pass the XY points as _addresses_ if the range is interrupted (*) ? . I made a macro which writes the necessary array formulas into the cell. It works as long you pass them as range or as values (which is not so a good idea as this is static). I also tried with a function but, besides this solution getting dependant on my addin, I got stuck, see my post <A target="_blank" HREF=http://www.wopr.com/cgibin/w3t/showthreaded.pl?Cat=&Board=vb&Number=46784&Search= true&Forum=All_Forums&Words=cri&Match=Username&Sea rchpage=0&Limit=25&Old=allposts&Main=46784>Array(A rghh_list)</A> in VBA.
(*) Because the datapoint lines of the XY chart follow the sorting order they will criss cross if the interpolated points are in the same series.

20010625, 19:33 #11
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
I don't know, but if I get around to implementing this approach, I will have to figure out a way, because the data I use for input is not consistently the same number of points, same starting point, and/or same ending point. If I work out a good way, I'll post it here.

20010816, 08:08 #12
 Join Date
 Dec 2000
 Posts
 140
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
JIMbythebay, you have already seen this, including the link here as more likely to be found than where I used it as example or making an "array of arrays"
<A target="_blank" HREF=http://www.wopr.com/cgibin/w3t/showthreaded.pl?Cat=&Board=xl&Number=55264&page=&v iew=&sb=&o=&vc=1#Post55264>http://www.wopr.com/cgibin/w3t/showthread...&vc=1#Post55264</A>

20010817, 19:43 #13
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
Hi and thanks! Yes, I was following that thread, to see if it had any use for me. Bottom line is, I have a lot more info now about how to accomplish what I had in mind three or so years ago, but there is still a lot housekeeping to perform because
A) The number of Y data points available to me varies from series to series, which means ...
[img]/forums/images/smilies/cool.gif[/img] ... I have to figure out where the data begins and ends, then feed those ranges into LinEst (otherwise, LinEst chokes on the empty cells)
Once this is done, I will use Hans' suggestion of using INDEX to pick out the specific coefficient, so that people who follow me will not have to learn about array entry of the LinEst function.

20010818, 06:11 #14
 Join Date
 Dec 2000
 Posts
 140
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
JIMbythebay, give my function a try, it will _not_ choke on empty cells. It was my main reason to do it, this way I can insert the interpolated points in the X range

20010821, 15:47 #15
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: 4th order poly fit
Hi cri,
OK, I tried it again. Earlier, I did not understand the strategy behind your code, and I failed to recognize that array entry is still needed. I got it working now, it is very nice indeed! Many thanks!