How to Use the LINEST Function in Google Sheets for Linear Regression and Statistical Modelling

While simple trendlines in Google Sheets charts provide a quick visual approximation of linear relationships, serious econometric forecasting, engineering calibration, and multivariate regression models require full analytical rigor. Manually computing slope coefficients, standard errors, and goodness-of-fit metrics across multi-variable datasets is mathematically tedious. Google Sheets provides a comprehensive, array-enabled statistical function designed specifically for this purpose: LINEST (Linear Estimation).

Understanding the LINEST Array Output

The LINEST function uses the method of ordinary least squares to compute a straight line that best fits your observed data points, modelling the mathematical relationship:

y = m1*x1 + m2*x2 + ... + mn*xn + b

Where $y$ represents the dependent response variable, $x_1 dots x_n$ represent independent explanatory variables, $m_1 dots m_n$ represent calculated slope coefficients, and $b$ represents the vertical axis intercept.

When the optional third argument verbose is enabled, LINEST dynamically outputs an expanded 5-row statistical matrix:

Output Row	Left Column(s)	Rightmost Column
Row 1	Slope coefficients ($m_n, m_{n-1}, dots, m_1$)	$y$-intercept ($b$)
Row 2	Standard error values for slopes ($se_n dots se_1$)	Standard error for intercept ($se_b$)
Row 3	Coefficient of determination ($R^2$)	Standard error for the $y$ estimate ($se_y$)
Row 4	$F$-statistic ($F$)	Degrees of freedom ($df$)
Row 5	Regression sum of squares ($ss_{reg}$)	Residual sum of squares ($ss_{resid}$)

Syntax and Parameter Rules

=LINEST(known_data_y, [known_data_x], [calculate_b], [verbose])

• known_data_y: The range of observed dependent values (single row or single column).
• known_data_x: The range of independent predictor variables. In multiple regression, this can span multiple adjacent columns. Must match the row count of known_data_y.
• calculate_b: Optional boolean. If TRUE (or omitted), $b$ is calculated normally. If FALSE, $b$ is forced to 0 ($y = mx$).
• verbose: Optional boolean. If TRUE, outputs the complete 5-row regression diagnostic matrix. If FALSE (default), returns only the top row of slope coefficients and intercept.

Step-by-Step Practical Example: Predicting Sales from Ad Spend

Suppose an e-commerce brand records monthly figures for Digital Ad Spend (£000s) in column A (cells A2:A11) and resulting Monthly Revenue (£000s) in column B (cells B2:B11):

Month	Ad Spend (X) — Col A	Revenue (Y) — Col B
1	10	52
2	14	68
3	18	80
4	22	95
5	26	110

1. Extracting Slope and Intercept

To calculate the baseline prediction equation, enter this formula in cell D2:

=LINEST(B2:B11, A2:A11, TRUE, FALSE)

Google Sheets spills two horizontal values across cells D2:E2:

• Cell D2: 3.62 (Slope $m$: each additional £1,000 in ad spend yields £3,620 in revenue).
• Cell E2: 16.14 (Intercept $b$: estimated baseline revenue without ad spend).

2. Generating the Full Diagnostic Matrix

To evaluate whether your model meets statistical significance benchmarks, set verbose to TRUE:

=LINEST(B2:B11, A2:A11, TRUE, TRUE)

Inspect cell D4 for the $R^2$ value (e.g. 0.984), confirming that 98.4% of variance in revenue is directly explained by your ad spend variable.

Extracting Individual Statistics with INDEX

Rather than spilling an entire 5×2 grid across your dashboard, use the INDEX function to extract specific metrics directly into structured KPI cards:

• Extract $R^2$ directly: =INDEX(LINEST(B2:B11, A2:A11, TRUE, TRUE), 3, 1)
• Extract $F$-statistic: =INDEX(LINEST(B2:B11, A2:A11, TRUE, TRUE), 4, 1)
• Extract Standard Error ($se_y$): =INDEX(LINEST(B2:B11, A2:A11, TRUE, TRUE), 3, 2)

Troubleshooting Common LINEST Errors

• #REF! Error: Occurs when the array expansion from LINEST collides with non-empty cells situated directly below or to the right of your formula cell. Ensure you have clear grid space before entering verbose queries.
• #VALUE! Error: Triggered when known_data_y and known_data_x contain mismatched row counts, or when non-numeric text strings are present in data ranges without clean cell sanitisation.
• Collinearity in Multi-Variable Models: If two independent $X$ variables are perfectly correlated (e.g. including both Celsius and Fahrenheit temperature columns), LINEST returns 0 or #NUM! for redundant slopes due to singular matrix inversion.