For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Statistical

LINEST Function

Uses the "least squares" method to calculate a straight line that best fits your data and returns an array that describes the line.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Syntax

LINEST(known_y's, known_x's, const, stats)

  • known_y's  Required. The range or array of y values you already know in the relationship y = mx + b . (From another perspective, a chart typically displays these "y" values in the vertical axis.) 

  • known_x's  Optional. The range or array of x values you typically know in the relationship y = mx + b . (From another perspective, "x" values are frequently date serial numbers, counters, or other numeric categories.) If 'known_x's' is omitted, it is assumed to be the array {1,2,3,...} that is the same size as 'known_y's'.

  • const  Optional. A logical value that specifies whether to force the constant b to equal 1.

    • If const is TRUE or omitted, then b is calculated normally.

    • If const is FALSE, b is set equal to 0, and the m-values are fitted to y = mx

  • stats  Optional. A logical value that specifies whether to return additional regression statistics.

    • If stats is FALSE or omitted, LOGEST returns only the m-coefficient and the constant b, as shown in the first two rows of the following table.

    • If stats is TRUE, LOGEST returns these regression statistics as an  array:

      Row Col Name Description
      1 1 m The m coefficient in y=mx+b
      1 2 b The b coefficient (which is the y-intercept) in y=mx+b
      2 1 se The standard error value for m
      2 2 seb The standard error value for b
      3 1 R2 The coefficient of determination
      3 2 sey The standard error of the y estimate
      4 1 F The F statistic
      4 2 df The degrees of freedom
      5 1 ssreg The regression sum of squares
      5 2 ssresid The residual sum of squares
       

Applies To

Excel 2003 and above

Examples

The following examples use this data:

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards