Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    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 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. #2
    2 Star Lounger
    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

  3. #3
    4 Star Lounger
    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 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. #4
    3 Star Lounger
    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.

  5. #5
    2 Star Lounger
    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.

  6. #6
    3 Star Lounger
    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.

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

  8. #8
    2 Star Lounger
    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

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

  10. #10
    2 Star Lounger
    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 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. #11
    3 Star Lounger
    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.

  12. #12
    2 Star Lounger
    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/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=55264&page=&v iew=&sb=&o=&vc=1#Post55264>http://www.wopr.com/cgi-bin/w3t/showthread...&vc=1#Post55264</A>

  13. #13
    3 Star Lounger
    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.

  14. #14
    2 Star Lounger
    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

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

Posting Permissions

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