Returns a table of statistics for a straight line that best fits a data set.

LINEST(yvalues, xvalues, allow_const, stats)

yvalues is a single row or column range specifying the y coordinates in a set of data points.

xvalues is a corresponding single row or column range specifying the x coordinates. If xvalues is omitted it defaults to 1, 2, 3, ..., n. If there is more than one set of variables xvalues may be a range with corresponding multiple rows or columns.

LINEST finds a straight line that best fits the data, using linear regression (the "least squares" method). With more than one set of variables the straight line is of the form .

if allow_const is FALSE the straight line found is forced to pass through the origin (the constant a is zero; ). If omitted, allow_const defaults to TRUE (the line is not forced through the origin).

to are the line gradients; a is the y-axis intercept.

to are the standard error values for the line gradients; is the standard error value for the y-axis intercept.

is the determination coefficient RSQ; is the standard error value for the y estimate.

F is the F statistic (F-observed value); df is the number of degrees of freedom.

is the regression sum of squares; is the residual sum of squares.

Example:

In the example above, cells A2:B8 contain the x,y values for a set of points. LINEST(B2:B8,A2:A8,1,1) returns the statistics for the best fit line through those points.

In the example above, you measure the floor area and count the windows of a sample of houses in the area, and make a table with the corresponding sale value (cells A2:C8). To predict the value of other houses in the area you might use: value = a + b1*floor_area + b2*num_windows, where a, b1 and b2 are constants. LINEST(A2:A8,B2:C8,1,1) returns appropriate statistics for that equation.

PRODUCT & FEATURES

CAPTCHA Protection

RESOURCES

© 2023 Zapof