Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting Coefficients for Trendlines (97/2000)

    Hello everybody!

    Today I have got a very interesting question, and I am really curious, if anybody can help. I would like to extract the coefficients for a trendline in a diagramm (polynomial, for instance). I found out, that it is possible for a linear trendline (table-function RGP, at least in the german version) and an exponentional trendline (function RKP). In combination with the function INDEX, it is possible to receive the different coefficients. Unfortunately up to now I did not find a similar function for other trendline-types...
    There is one workaround, but it is not very smart: Draw the points into an xy-plot, generate a trendline and force Excel to show the formula in the diagram. The numberformat may be determined, and so it is possible to copy the coefficients from the formula-textfield. But this means handwork, and cannot be automized.
    Does anybody know a more clever way for this?

    I am really looking forward to your answers!

    Many greetings, Porley

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Coefficients for Trendlines (97/2000)

    You should be able to extract other types using worksheet functions too. For polynomal linear equations, you must use not just the x'es as argument, but the x^2 and x^3, etcetera too. Which type would you want?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extracting Coefficients for Trendlines (97/2000)

    In addition to Jan Karel Pieterse's reply, here is a workbook with formulas to calculate the coefficients for a 10th order polynomial trend line, using X^2, X^3 etc. and the LINEST function.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting Coefficients for Trendlines (97/2000)

    Thanks a lot for your help. Especially for the Worksheet! It really did help a lot. Now I am able to integrate this function into my workbook.

    Best regards,

    Porley <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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