LINEST Function

Returns the coefficients and statistics of a least-squares straight-line fit.

Category

Statistical

Available in

All versions; spills in Microsoft 365 and Excel 2021+ (older versions: select the output cells and press Ctrl+Shift+Enter)

Returns

An array

Taught hands-on in Data Analytics · Relationships & Trends module

Overview

LINEST fits y = m × x + b (or, with several x columns, y = m₁x₁ + m₂x₂ + … + b) and returns the coefficients as an array. With one x, the first row is the slope then the intercept.

Set stats to TRUE for a five-row block of regression statistics: standard errors, R², the standard error of y, the F statistic and degrees of freedom, and the regression and residual sums of squares.

With several x columns the coefficients come back in reverse order — the last x column's slope first, the intercept last.

Syntax

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

Arguments

ArgumentRequired?What it does
known_y'sYesThe y values.
known_x'sNoOne or more columns of x values. If left out, 1, 2, 3 … is used.
constNoTRUE or omitted calculates the intercept; FALSE forces it to 0.
statsNoTRUE returns the full statistics block; FALSE or omitted returns only the coefficients.

Examples

Example 1 — Full regression output

One formula in E2 spills ten results, laid out as Excel always arranges them.

E2fx=LINEST(C2:C9,B2:B9,TRUE,TRUE)
ABCDEF
1MonthAd spend ($k)Sales ($k)Slope (m)Intercept (b)
2Jan221Coefficient4.107112.2143
3Feb325Std error0.20671.1035
4Mar533R² | Std err y0.98501.0938
5Apr428F | df394.786
6May638SS reg | SS resid472.327.18
7Jun740
8Jul846
9Aug531
=LINEST(C2:C9,B2:B9,TRUE,TRUE) Explain

Slope 4.11 with a standard error of 0.21, R² 0.985 and a large F statistic: the relationship is very unlikely to be chance.

Example 2 — Pick out one number

Wrap it in INDEX to return a single value.

=INDEX(LINEST(C2:C9,B2:B9),1) Explain

Returns the slope, 4.1071. INDEX(LINEST(C2:C9,B2:B9),2) returns the intercept, and INDEX(LINEST(C2:C9,B2:B9,TRUE,TRUE),3,1) returns R².

Example 3 — Two drivers at once

With ad spend in B and price in C and sales in D, both x columns go in one range.

=LINEST(D2:D9,B2:C9,TRUE,TRUE) Explain

The first row returns the price coefficient, then the ad-spend coefficient, then the intercept.

Common mistakes

  • #SPILL! Something is in the way of the output block. Clear the cells to the right and below.
  • Reversed order with several x columns. The coefficients are listed from the last x column to the first.
  • #VALUE! A cell in the y or x range is empty or holds text. LINEST needs a complete block of numbers.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.