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

  2. #2
    3 Star Lounger
    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 Ctrl-Shift-Enter (instead of just plain-old 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 Ctrl-Shift-Enter:
    <pre>=TRANSPOSE(LINEST(YRange,XRange^{1,2,3},TRUE, TRUE))
    </pre>


    3)
    If you don't like array-entry (Ctrl-Shift-Enter), 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!).

  3. #3
    Super Moderator WebGenii's Avatar
    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 1-Click 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=link|description|[/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>
    Attached Images Attached Images
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

Posting Permissions

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