Results 1 to 6 of 6

Thread: What are an improvements of LINEST function in Excel 2003?

  1. #1
    Join Date
    Jul 2006
    Posts
    182

    What are an improvements of LINEST function in Excel 2003?

    Hi friends,
    I have done some basic things of the statistical functions. I have been told to write about an improvements of the Linest function. I have an idea about the Linest function but I don't know the improvements of it. So thought to ask you guys about it. Please tell me what are an improvements of LINEST function in Excel 2003? Hope that you all understood what I want to say.?!? Please reply me soon.!!
    "Yea though I walk through the valley of the shadow of death... I will fear no evil." -Psalms 23

    K8N Diamond Plus (BIOS v1.2)
    AMD Athlon 64 X2 4400+
    Antec TruControl 550W
    NVidia GeForce 7900GT (NGO v1.8466 BETA)
    OCZ Platinum 2x1GB (2-3-2-5)
    SATA: WD740
    PATA: 2xWD2500, WD1200, NEC DVD/RW

  2. #2
    Join Date
    Apr 2008
    Posts
    4,644

    Re: What are an improvements of LINEST function in Excel 2003?

    The following improvements have been made: the correction formula for the sum of squares when the third argument LINEST set to FALSE and transition to the QR decomposition method to determine the regression coefficients. The QR decomposition has two advantages :
    1. better numerical stability (rounding errors usually smaller);
    2. analysis problems of collinearity.

    Collinearity remains a major challenge, especially after having performed the tests on data-sets from NIST.

  3. #3
    Join Date
    May 2008
    Posts
    4,348

    Re: What are an improvements of LINEST function in Excel 2003?

    Regardless of the value of the third argument, LINEST was calculated using a method that ignores the problems of collinearity. The presence of collinearity caused rounding errors, and standard errors of regression coefficients and degrees of freedom inadequate. The rounding issues were sometimes so large that LINEST filled its output table with # NUM!. Generally, LINEST gives satisfactory results when the following conditions are met :
    • No column of the explanatory variables are collinear (or nearly collinear).
    • The third argument LINEST is TRUE or omitted.

    The resolution of regression coefficients using the "normal equations" However, more likely to cause rounding errors than using the method of QR decomposition used in Excel 2003 and in later versions of Excel. Even if these factors cause more rounding errors, they do not pose a risk in most practical cases.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,089

    Re: What are an improvements of LINEST function in Excel 2003?

    LINEST has been greatly improved in Excel 2003 and in later versions of Excel. If you are using an earlier version of Excel, make sure the columns of explanatory variables are not collinear before using LINEST. It is recommended to use the workaround suggested in this article when the third argument LINEST set to FALSE. Note that collinearity does not pose a problem in a small percentage of cases and appeals to LINEST when the third argument set to FALSE are also relatively rare in practice.

  5. #5
    Join Date
    May 2008
    Posts
    353

    Re: What are an improvements of LINEST function in Excel 2003?

    Earlier versions of Excel gives satisfactory results with LINEST in the absence of collinearity or when the third argument LINEST is TRUE or omitted. Improvements in LINEST concern the linear regression tool of Analysis ToolPak calling LINEST and related functions include :
    • TREND
    • LOGEST
    • GROWTH

  6. #6
    Join Date
    Aug 2006
    Posts
    287

    Re: What are an improvements of LINEST function in Excel 2003?

    LINEST used an incorrect formula to find the total sum of squares when the third argument LINEST was set to FALSE. This formula gave erroneous results in the sum of squares regression, and for outputs that depend on R squared and F statistic. The columns of variables form a matrix. If the intersection is adjusted, there is actually an additional column 1 that is not on your worksheet. The QR decomposition determines the order of this matrix. The preceding formulas for the DF regression must be replaced by the following formulas :
    1. If "adjustment" Regression DF equals the order of the columns of the matrix of explanatory variables minus one'
    2. In case of "non-adjustment" Regression DF equals the order of the columns of the matrix of explanatory variables.
    Dimension 1100 (FMY032J) mini-tower
    2.53ghz Intel Pentium 4
    80 gig nfts HDD
    512 RAM
    Main circuit board: Dell 0CF458
    BIOS: Dell A00
    Display: Intel(R) 82865G Graphics Controller [Display adaptor]
    Multimedia: Sound MAX Integrated Digital Audio
    Windows XP Home SP2

Similar Threads

  1. Replies: 6
    Last Post: 11-03-2012, 02:33 PM
  2. Description of the LINEST function in Excel 2003
    By PsYcHo 1 in forum Windows Software
    Replies: 5
    Last Post: 13-03-2010, 04:47 AM
  3. How LOGEST interacts with LINEST in Excel?
    By MarceloQuad in forum Windows Software
    Replies: 5
    Last Post: 13-03-2010, 04:22 AM
  4. How TREND interacts with LINEST in Excel?
    By Vandam in forum Windows Software
    Replies: 5
    Last Post: 13-03-2010, 03:31 AM
  5. Replies: 5
    Last Post: 11-03-2010, 05:09 AM

Posting Permissions

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