# Thread: 4th order poly fit

1. ## 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 function-solving, etc.

I wrote a VBA routine that performs this automatically. Someone asked me if there is some other, not-quite-so-severely-manual 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!

2. ## 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

3. ## 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 X-data, with X^2, one with X^3 and one with X^4. Then choose Tools >> Data Analysis, select the Range for the Y-varaible and select the Range, including all columns with X-data for the X-input. 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 R-square.

You can use the Excel function LINEST, but you'll need to prepare the X-data in the same way as described above.

4. ## 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.

5. ## 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.

6. ## 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.

7. ## 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.

8. ## 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

9. ## 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!

10. ## 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 add-in, I got stuck, see my post <A target="_blank" HREF=http://www.wopr.com/cgi-bin/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.

11. ## 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.

12. ## 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"

13. ## 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.

14. ## 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

15. ## 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!

#### Posting Permissions

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