Google Sheets

LINEST function in Google Sheets

Given partial data about a linear trend, calculates various parameters about the ideal linear trend using the least-squares method.

=LINEST(known_data_y, [known_data_x], [calculate_b], [verbose])

LINEST syntax and parameters

Four arguments, one required.

ParameterTypeUseDescription

known_data_yarrayRequiredThe array or range containing dependent (y) values that are already known, used to curve fit an ideal linear trend. If known_data_y is a two-dimensional array or range, known_data_x must have the same dimensions or be omitted. If known_data_y is a one-dimensional array or range, known_data_x may represent multiple independent variables in a two-dimensional array or range. I.e. if known_data_y is a single row, each row in known_data_x is interpreted as a separated independent value, and analogously if known_data_y is a single column.

known_data_xarrayOptional[ OPTIONAL - {1,2,3,...} with same length as known_data_y by default ] - The values of the independent variable(s) corresponding with known_data_y. If known_data_y is a one-dimensional array or range, known_data_x may represent multiple independent variables in a two-dimensional array or range. I.e. if known_data_y is a single row, each row in known_data_x is interpreted as a separated independent value, and analogously if known_data_y is a single column. Note: For multiple independent variables, the order of the output parameters are corresponding to the input variables in reverse.

calculate_bbooleanOptional[ OPTIONAL - TRUE by default ] - Given a linear form of y = m*x+b, calculates the y-intercept (b) if TRUE. Otherwise, forces b to be 0 and only calculates the m values if FALSE, i.e. forces the curve fit to pass through the origin.

verbosebooleanOptional[ OPTIONAL - FALSE by default ] - A flag specifying whether to return additional regression statistics or only the linear coefficients and the y-intercept (default). If verbose is TRUE, in addition to the set of linear coefficients for each independent variable and the y-intercept, LINEST returns The standard error for each coefficient and the intercept, The coefficient of determination (between 0 and 1, where 1 indicates perfect correlation), Standard error for the dependent variable values, The F statistic, or F-observed value indicating whether the observed relationship between dependent and independent variables is random rather than linear, The degrees of freedom, useful in looking up F statistic values in a reference table to estimate a confidence level, The regression sum of squares, and The residual sum of squares.

known_data_yarrayRequired
The array or range containing dependent (y) values that are already known, used to curve fit an ideal linear trend. If known_data_y is a two-dimensional array or range, known_data_x must have the same dimensions or be omitted. If known_data_y is a one-dimensional array or range, known_data_x may represent multiple independent variables in a two-dimensional array or range. I.e. if known_data_y is a single row, each row in known_data_x is interpreted as a separated independent value, and analogously if known_data_y is a single column.
known_data_xarrayOptional
[ OPTIONAL - {1,2,3,...} with same length as known_data_y by default ] - The values of the independent variable(s) corresponding with known_data_y. If known_data_y is a one-dimensional array or range, known_data_x may represent multiple independent variables in a two-dimensional array or range. I.e. if known_data_y is a single row, each row in known_data_x is interpreted as a separated independent value, and analogously if known_data_y is a single column. Note: For multiple independent variables, the order of the output parameters are corresponding to the input variables in reverse.
calculate_bbooleanOptional
[ OPTIONAL - TRUE by default ] - Given a linear form of y = m*x+b, calculates the y-intercept (b) if TRUE. Otherwise, forces b to be 0 and only calculates the m values if FALSE, i.e. forces the curve fit to pass through the origin.
verbosebooleanOptional
[ OPTIONAL - FALSE by default ] - A flag specifying whether to return additional regression statistics or only the linear coefficients and the y-intercept (default). If verbose is TRUE, in addition to the set of linear coefficients for each independent variable and the y-intercept, LINEST returns The standard error for each coefficient and the intercept, The coefficient of determination (between 0 and 1, where 1 indicates perfect correlation), Standard error for the dependent variable values, The F statistic, or F-observed value indicating whether the observed relationship between dependent and independent variables is random rather than linear, The degrees of freedom, useful in looking up F statistic values in a reference table to estimate a confidence level, The regression sum of squares, and The residual sum of squares.

LINEST examples

Formulas you'll actually reuse.

Slope and intercept of monthly revenue against month number:
```
=LINEST(B2:B13, A2:A13)
```
Result{412.5, 8120}

Project month 13 from the fitted line:

=INDEX(LINEST(B2:B13, A2:A13), 1, 1) * 13 + INDEX(LINEST(B2:B13, A2:A13), 1, 2)

Result13482.5

LINEST in Excel

Same name — your formula ports as-is.

Try LINEST in the playground

Edit the example — nothing to install.

Preloaded with the LINEST formula from Example 1 — change anything and watch it respond.

Loading the editor…

LINEST errors

What they mean — and the fixes.

#REF!
known_data_y and known_data_x have different lengths — both ranges must cover the same number of points.

Related functions

More ways Google Sheets gets this done.