Practice free

LINEST function

LINEST runs a least-squares regression and returns an array with the slope or slopes and the intercept of the best-fit line, plus optional statistics. It allows multiple x columns.

Example

Read it as a sentence: fit a straight line of the G1:G5 values against the F1:F5 values and return the slope, which gives 0.6.

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

known_y's (required)
Known y values.
known_x's (optional)
Known x values; one or more columns.
const (optional)
TRUE (default) calculates the intercept; FALSE forces it to 0.
stats (optional)
TRUE returns extra regression statistics.

Returns: An array with the slope(s) and intercept of the best-fit line, plus optional statistics.

One LINEST formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=INDEX(LINEST({2;4;5;4;5},{1;2;3;4;5}),1,1) 0.6 INDEX slope
=LINEST(Data!G1:G5,Data!F1:F5) 0.6 Full result (spills)
=LINEST(Data!G1:G5) 0.6 Y only
=LINEST(Data!G1:G5,Data!F1:F5,FALSE) 1.2 Const FALSE
=LINEST(Data!G1:G5,Data!F1:F5,TRUE,TRUE) 0.6 Stats TRUE (spills 5x2)
=LINEST(Data!G1:G5,Data!J1:J5) 0 Constant x
=LINEST(Data!G1:G5,Data!K1:K3) #REF! Different sizes
=LINEST(Data!B1:B5,Data!F1:F5) #VALUE! Text in y

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Coefficients come out in reverse order of the x columns, intercept last.
  • Spills in 365; older versions need Ctrl+Shift+Enter.
  • Use INDEX to pull a single value.

Where it works

Excel
LINEST is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .