MS Excel: LINEST Function (WS)

This Excel tutorial explains how to use the Excel LINEST function with syntax and examples.

Description

The Microsoft Excel LINEST function uses the least squares method to calculate the statistics for a straight line and returns an array describing that line. The LINEST function uses the following line equation:

y = mx + b                  (for a single range of x values)
y = m1x1 + m2x2 + ... + b   (for multiple ranges of x values)

Syntax

The syntax for the Microsoft Excel LINEST function is:

LINEST( y_values, [x_values], [constant], [additional_statistics] )

Parameters or Arguments

y_values is the known set of "y values" from the line equation.
x_values is the optional. It is the known set of "x values" from the line equation. If this parameter is omitted, x_values is assumed to be {1,2,3,...} with the same number of values as y_values.
constant is optional. It is either TRUE or FALSE. If this parameter is omitted, the function will assume TRUE. If constant is TRUE, b in the line equation is calculated normally. If constant is FALSE, b becomes 0 so that the line equation is evalulated as y = mx.
additional_statistics is optional. It is either TRUE or FALSE. If this parameter is omitted, the function will assume FALSE. If additional_statistics is TRUE, the function will return additional regression statistics. If additional_statistics is FALSE, the function will returns only m coefficients and b constant.

Applies To

The LINEST function can be used in the following versions of Microsoft Excel:

• Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Excel Function

The LINEST function can be used in Microsoft Excel as the following type of function:

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel LINEST function examples and explore how to use the LINEST function as a worksheet function in Microsoft Excel:
This first example we have entered the y values in column A (cells A2 through A6) and the x values in column B (cells B2 through B6). Then we have entered the following LINEST function in both cells D2 and E2 as follows:

=LINEST(A2:A6,B2:B6,TRUE,FALSE)

The LINEST functions are written as array formulas. When creating your array formula, you need to do the following:

1. Enter the formulas in both cells D2 and E2
2. Highlight cells D2 and E2
3. Press F2 (in Windows) or CONTROL-U (on Mac)
4. Press Ctrl+Shift+Enter

This creates {} brackets around your formulas as follows:

{=LINEST(A2:A6,B2:B6,TRUE,FALSE)}

You can see how the formula look in the images below.


The value in cell D2 returns the slope of 0.5 and the value in cell E2 returns the y-intercept of 0.
This formula could also be written as follows (entering values instead of cell ranges):

=LINEST({2,3,5,7,10},{4,6,10,14,20},TRUE,FALSE)

The LINEST functions are written as array formulas. When creating your array formula, you need to do the following:

1. Enter the formulas in both cells D2 and E2
2. Highlight cells D2 and E2
3. Press F2 (in Windows) or CONTROL-U (on Mac)
4. Press Ctrl+Shift+Enter

This creates {} brackets around your formulas as follows:

{=LINEST({2,3,5,7,10},{4,6,10,14,20},TRUE,FALSE)}