Menu

LINEST Function (LibreOffice Calc)

Math Advanced LibreOffice Calc Introduced in LibreOffice 3.0
regression statistics data-analysis modeling forecasting linear-regression

The LINEST function in LibreOffice Calc performs linear regression and returns statistics describing the best-fit line. This guide explains syntax, array behavior, regression output, examples, errors, and best practices.

Compatibility

▾

What the LINEST Function Does ▾

  • Computes linear regression for one or more independent variables
  • Returns slope(s), intercept, and full regression statistics
  • Supports multi-variable regression
  • Works as an array function
  • Ideal for forecasting, modeling, and statistical analysis
  • Works across sheets

LINEST is one of Calc’s most advanced statistical tools.

Syntax ▾ 

LINEST(known_y; known_x; const; stats)

Where:

  • known_y — dependent variable (Y values)
  • known_x — independent variable(s) (X values)
  • const — TRUE = calculate intercept; FALSE = force intercept = 0
  • stats — TRUE = return full regression statistics; FALSE = return only slope/intercept
LINEST returns an array.
Older Calc versions require Ctrl+Shift+Enter.
Modern Calc spills automatically.

Output Structure ▾

When stats = FALSE:

Column 1 Column 2
Slope Intercept

When stats = TRUE, LINEST returns a 5-row array:

Row Meaning
1 Slope(s), Intercept
2 Standard error of slope(s), Standard error of intercept
3 R², Standard error of Y estimate
4 F-statistic, Degrees of freedom
5 Regression sum of squares, Residual sum of squares

Example (single variable):

slope    intercept
se_slope se_intercept
r2       se_y
F        df
ss_reg   ss_resid

Basic Examples ▾

Simple linear regression (Y vs X)

=LINEST(B1:B10; A1:A10)

Regression with intercept forced to zero

=LINEST(B1:B10; A1:A10; FALSE)

Full regression statistics

=LINEST(B1:B10; A1:A10; TRUE; TRUE)

Regression across sheets

=LINEST(Sheet1.B1:B50; Sheet2.A1:A50)

Multi‑Variable Regression ▾

Two independent variables

=LINEST(Y1:Y20; X1:Z20; TRUE; TRUE)

Where X1:Z20 contains two columns of predictors.

Regression with multiple predictors and no intercept

=LINEST(Y1:Y20; X1:Z20; FALSE; TRUE)

Advanced Examples ▾

Predict Y using regression coefficients

Slope:

=INDEX(LINEST(B1:B10; A1:A10); 1)

Intercept:

=INDEX(LINEST(B1:B10; A1:A10); 1; 2)

Prediction:

=INDEX(LINEST(B1:B10; A1:A10); 1)*X + INDEX(LINEST(B1:B10; A1:A10); 1; 2)

Use LINEST inside TREND for forecasting

=TREND(B1:B10; A1:A10; A11:A20)

Regression ignoring errors

=LINEST(IF(ISNUMBER(B1:B10); B1:B10); A1:A10)

(Confirm with Ctrl+Shift+Enter in older Calc.)

Regression on visible cells only (filtered data)

Use helper column:

=SUBTOTAL(103; OFFSET(A1; ROW(A1:A100)-ROW(A1); 0))

Then filter X/Y using FILTER before passing to LINEST.

Regression with logarithmic transform

=LINEST(LN(B1:B10); A1:A10)

Regression for exponential growth (log-linear)

=LINEST(LN(Y1:Y20); X1:X20; TRUE; TRUE)

Common Errors and Fixes ▾

Err:502 — Invalid argument

Occurs when:

  • X and Y ranges have mismatched lengths
  • Non-numeric text included
  • Empty arrays passed incorrectly

Err:504 — Parameter error

Occurs when:

  • Semicolons are incorrect
  • Array dimensions are invalid

LINEST returns only slope/intercept when stats expected

Occurs when:

  • Output range too small
  • Not entered as array in older Calc

R² or F-statistic seems wrong

Possible causes:

  • X values not scaled
  • Multicollinearity in predictors
  • Non-linear relationship

LINEST returns #N/A in some cells

Occurs when:

  • Not enough data points
  • Forced intercept creates invalid regression
  • Predictors are linearly dependent

Best Practices ▾

  • Use LINEST for serious regression modeling
  • Use TREND or FORECAST for simpler predictions
  • Always inspect R², F-statistic, and residuals
  • Avoid multicollinearity in multi-variable regression
  • Log-transform data for exponential relationships
  • Use named ranges for cleaner formulas
LINEST is the most powerful regression tool in Calc — once you master it, you can build full statistical models directly inside your spreadsheet.

Related Functions

Copyright 2026. All rights reserved.