+ Reply to Thread
Results 1 to 11 of 11

LINEST function for polynomials

  1. #1
    Registered User
    Join Date
    10-07-2009
    Location
    NZ
    MS-Off Ver
    Excel 2007
    Posts
    2

    Unhappy LINEST function for polynomials

    I am trying to use the LINEST function to get a "n" degree polynomial regression. The problem appears when i have more than 1 independant variables. I can't seem to list x1 to the power of a polynomial, in conjunction with x2, nor can i list x1 and x2 to the power of a polynomial.

    I have tried these formulas:
    =linest(y range,(x1,x2)^column(a1:c1),,true) or
    =linest(y range,[x1^column(a1:c1),x2],,true)

    Does someone know how i can make it work with n degree polynomial, and 2 independent variables? HELP!

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Help on LINEST function for polynomials

    Hi,

    There's some really good stuff here
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Help on LINEST function for polynomials

    There may be a way to do it more compactly, but you can put the powers in separate columns:
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    10-07-2009
    Location
    NZ
    MS-Off Ver
    Excel 2007
    Posts
    2

    Exclamation Re: LINEST function for polynomials

    Hi thanks for your tips~

    Sweep: that was useful except when i tried a 2 degree polynomial with 2 independent factors it only returned 3 coefficient including the constant, shouldn't it return at least 4, 1 constant, 1 for x^2, 1 for x and for t?

    shg: your way seems the only way i can go about it now! thanks for that, i have not thought abt it doing it that way.

  5. #5
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: LINEST function for polynomials

    Sorry, a bit late.

    The second degree polynomial you are using has the terms

    AX^2+BX+DZ+EZ^2+K

    You can avoid the auxilliary columns if you combine the exponents of the variables. Using shg's layout without the columns C, D:

    =LINEST(E2:E16,A2:A16^{2,1,0,0}*B2:B16^{0,0,1,2})

    Remarks:

    1 - I did not consider the case 0^0. If you have that case add an IF to the formula

    2 - (Important) A quadratic polynomial with 2 variables has usually a 6th term (like in the conics) that you did not consider:

    AX^2+BX+CXZ+DZ+EZ^2+K

    In that case you add the new term to the arrays:

    =LINEST(E2:E16,A2:A16^{2,1,1,0,0}*B2:B16^{0,0,1,1,2})

    HTH

  6. #6
    Registered User
    Join Date
    03-15-2013
    Location
    Nederland
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: LINEST function for polynomials

    I have a question very much related to this thread, that's why I didn't create a new one:

    When doing a polynomial regression for two (independent) variables, like here, one should use an array after the input-variables to indicate the degree of the polynomial intended for that variable. I do not get how one should use this array (like the array {2,1,0,0} above)

    What I would like to do is making a regression for two variables, one probably linearly and one second order related to Y. What I tried to do is:

    =LINEST(X1, X2^{1,2},TRUE,TRUE)

    for the intended model Y = A*X1 + B*X2^2 + C*X2 + D*X1*X2 + b

    I would have thought that this would work, but the resulting LINEST-array gives me #value errors.

    How would I use the ^{1,2,3,etc} array?

    Thanks a lot!

  7. #7
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: LINEST function for polynomials

    dirxess,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  8. #8
    Registered User
    Join Date
    03-15-2013
    Location
    Nederland
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: LINEST function for polynomials

    Ok, I'm sorry, Ill do that.

  9. #9
    Registered User
    Join Date
    03-29-2013
    Location
    Zagreb
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: LINEST function for polynomials

    Quote Originally Posted by lecxe View Post
    You can avoid the auxilliary columns if you combine the exponents of the variables. Using shg's layout without the columns C, D:

    =LINEST(E2:E16,A2:A16^{2,1,0,0}*B2:B16^{0,0,1,2})
    Hello, i tried this solution and its not work. I get err. #VALUE.
    Can you help me?

    I have the same approach like sgh example, but that is not good enough.

    Thanks!

  10. #10
    Registered User
    Join Date
    03-15-2013
    Location
    Nederland
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: LINEST function for polynomials

    I started a new thread on this. The user lexce helped me getting it to work. It may also help you.

  11. #11
    Registered User
    Join Date
    03-29-2013
    Location
    Zagreb
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: LINEST function for polynomials

    Thanks a lot friend!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1