Author: Oscar Cronquist Article last updated on October 26, 2018

The LINEST function returns an array of values representing the parameters of a straight line based on the "least squares" method. The LINEST function also lets you combine other functions in order to calculate polynomial, logarithmic, exponential and power series. This function must be entered as an array formula.

Array formula in cell range C15:D15:

=LINEST(B3:B12)

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

The following image shows a straight line based on the m and b parameters that the LINEST function calculated in cell C15 and D15.

The blue dots are the known y's in column B in the top image.

Excel Function Syntax

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

Arguments

y = mx + b is the function used to calculate the straight line that fits the data best using "least squares" method.

known_y's Required.
Single column - Each row is a separate variable.
Single row - Each column is a separate variable.
[known_x's] Optional. Known x points, default values are 1, 2, 3, ...
[const] Optional. A boolean value determining if constant b is equal to 0 (zero).
TRUE - constant b is calculated. Default.
FALSE - constant b is 0 (zero).
[stats] Optional. A boolean value determining whether to calculate additional regression statistics.
TRUE - Returns additional regression statistics. {mn, mn-1, ..., m1, b;sen, sen-1, ..., se1, seb;r2, sey;F, df;ssreg, ssresid}
FALSE - returns only m and b.

The following table shows what the LINEST function returns if [stats] argument is TRUE.

Statistic Description
se1, se2, ..., sen The standard error values.
seb The standard error value for the constant b.
seb returns #N/A when const argument is FALSE.
r2 The coefficient of determination. A perfect correlation is 1 and 0 (zero) means no correlation based on comparing the actual and the LINEST functions estimated y-values.
sey The standard error for the estimated y-values.
F The F statistic, or the F-observed value. Determines if the observed relationship between the dependent and independent variables occurs by chance.
df The degrees of freedom assists you in finding F-critical values, then compare the values to the F statistic to get the confidence level for the model.
ssreg The regression sum of squares.
ssresid The residual sum of squares.

Comments

The SLOPE and INTERCEPT function lets you also find the parameters m and b needed to describe the straight line function.

y = mx + b

The TREND function calculates the y and x-values needed to plot the straight line function and the growth function for exponential curves.

The F-test value from the LINEST function differs from the FTEST function:

  • F-test (LINEST function): F statistic
  • FTEST function: probability