fx
FunctionPedia
Quick Answer

LINEST Function

Returns regression statistics (slope, intercept, and more) as an array.

✓ Excel✓ Google SheetsExcel All versions

Syntax

LINEST
(known_ys, [known_xs], [const], [stats])

Parameters

ParameterDescriptionRequired
known_ysThe dependent (y) values of the known data.Required
known_xsThe independent (x) values; for multiple regression supply several columns. Defaults to 1,2,3,... if omitted.Optional
constTRUE (or omitted) includes an intercept; FALSE forces the line through the origin.Optional
statsFALSE (or omitted) returns just slope and intercept; TRUE adds standard errors, R-squared, F, df, and sums of squares.Optional

Basic Example

Get slope and intercept of a fitted line

=LINEST({2,3,5,7,11},{1,2,3,4,5})
Result{2.2, -1}

The fitted line is y = -1 + 2.2x, so LINEST returns slope 2.2 and intercept -1 (entered as an array in two cells).

Advanced Examples

Example 1: Full regression statistics

Need diagnostics, not just the line

Set stats to TRUE for a 2-row by 5-column array

=LINEST({2,3,5,7,11},{1,2,3,4,5},TRUE,TRUE)
Result: {2.2, -1, 0.30551, 1.0132, 0.94531; 0.96609, 51.857, 3, 48.4, 2.8}
Row 1 is slope, intercept, standard error of slope, standard error of intercept, R-squared. Row 2 is standard error of y, F-statistic, degrees of freedom, regression sum of squares, residual sum of squares. In older Excel enter with Ctrl+Shift+Enter; in Microsoft 365 it spills.

Example 2: Multiple regression

Two independent variables

Pass two columns as known_xs

=LINEST(C2:C20,A2:B20,TRUE,TRUE)
Result: Array of coefficients for both x-columns plus intercept and diagnostics
With n x-columns, LINEST returns n+1 coefficients in the first row (one slope per column, then the intercept).

How LINEST Works

LINEST performs ordinary least-squares regression, minimizing the sum of squared residuals. With a single x it fits y = a + bx and returns the slope and intercept (and, with stats=TRUE, the full diagnostic block). With multiple x-columns it returns one slope per column plus the intercept. Because it is an array function, the results spill into adjacent cells.

1
Select output area
For stats=TRUE, select a 2-row by 5-column range; otherwise two cells.
2
Enter formula
Type =LINEST(known_ys, known_xs, TRUE, TRUE) and press Enter (or Ctrl+Shift+Enter in legacy Excel).
3
Read results
Use the slope/intercept to build the equation; use R-squared and F to judge fit.

Important Notes & Limitations

  • Returns an array, so in pre-dynamic Excel it must be entered as an array formula with enough empty cells to spill into.

  • known_xs and known_ys must have the same number of rows, or #REF! is returned.

  • If the x-columns are linearly dependent, the function returns #REF! because the regression cannot be solved.

Common Errors & Fixes

#REF!Array sizes mismatch or the x-columns are collinear (linearly dependent).

Fix: Match row counts and remove redundant/duplicate x-columns.

Wrong values spillOutput range too small or not entered as an array.

Fix: Select a large enough range and use Ctrl+Shift+Enter (legacy Excel).

Download Practice File

Practice LINEST with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the LINEST function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

What does the basic LINEST return?
Just two values: the slope and the intercept of the fitted line, in that order.
How do I get R-squared and other stats?
Set the fourth argument (stats) to TRUE to add standard errors, R-squared, F, df, and sums of squares.
How is LINEST related to SLOPE/INTERCEPT/FORECAST?
SLOPE, INTERCEPT, and FORECAST use the same least-squares line; LINEST returns all of it (plus diagnostics) in one array.