Browse

INTERCEPT Function (LibreOffice Calc)

Math Beginner LibreOffice Calc LibreOffice 3.0

The INTERCEPT function in LibreOffice Calc returns the Y-intercept of the linear regression line through a set of data points. This guide explains syntax, examples, regression logic, errors, and best practices.

The **INTERCEPT** function returns the **Y-intercept (b)** of the best-fit linear regression line through a set of data points. It is one of the core components of linear modeling in Calc and is used internally by FORECAST, TREND, and LINEST.

This guide provides syntax, examples, regression logic, common errors, and best practices, making it one of the most complete INTERCEPT references available for LibreOffice Calc.

What the INTERCEPT Function Does

  • Calculates the Y-intercept of the regression line
  • Uses least-squares linear regression
  • Works with numeric X/Y pairs
  • Useful for forecasting, trend analysis, and modeling
  • Works across sheets

The regression line is:

y = m*x + b

INTERCEPT returns b.

Syntax

INTERCEPT(known_y; known_x)

Where:

  • known_y — dependent variable (Y values)
  • known_x — independent variable (X values)
X and Y ranges must be the same size.

Basic Examples

Intercept of Y vs X

=INTERCEPT(B1:B10; A1:A10)

Intercept across sheets

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

Intercept using named ranges

=INTERCEPT(Sales; Months)

Intercept with dates as X-values

=INTERCEPT(B1:B100; A1:A100)

(Calc automatically converts dates to serial numbers.)

Advanced Examples

Manual FORECAST equivalent using SLOPE + INTERCEPT

=SLOPE(B1:B10; A1:A10) * 11 + INTERCEPT(B1:B10; A1:A10)

Intercept ignoring errors

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

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

Intercept using filtered (visible) data only

Use SUBTOTAL helper column to filter X/Y before passing to INTERCEPT.

Intercept after removing outliers

=INTERCEPT(FILTER(B1:B100; B1:B100<1000); FILTER(A1:A100; B1:B100<1000))

Intercept for time-series forecasting

=INTERCEPT(Sales; Dates)

Intercept for normalized data

=INTERCEPT((B1:B10 - AVERAGE(B1:B10)); (A1:A10 - AVERAGE(A1:A10)))

How INTERCEPT Calculates the Regression Constant

INTERCEPT uses the least-squares formula:

[ b = \bar{y} - m \cdot \bar{x} ]

Where:

  • ( m ) = slope (from SLOPE or LINEST)
  • ( \bar{x} ) = mean of X
  • ( \bar{y} ) = mean of Y

This is the same intercept used by LINEST, TREND, and FORECAST.

Common Errors and Fixes

Err:502 — Invalid argument

Occurs when:

  • X and Y ranges have different sizes
  • Non-numeric text included
  • One of the ranges is empty

Err:504 — Parameter error

Occurs when:

  • Semicolons are incorrect
  • Range references malformed

INTERCEPT returns unexpected value

Possible causes:

  • Relationship is not linear
  • Outliers distort regression
  • X-values not aligned with Y-values
  • X-values have zero variance (all X are identical)

INTERCEPT differs from LINEST

They are identical — LINEST simply returns more statistics.

Best Practices

  • Use INTERCEPT when you need only the regression intercept
  • Use SLOPE alongside INTERCEPT for manual predictions
  • Use TREND for multi-value forecasting
  • Use LINEST for full regression diagnostics
  • Remove outliers before modeling
  • Plot your data to confirm linearity
  • Use named ranges for cleaner formulas
INTERCEPT gives you the baseline of your trend — the point where your regression line crosses the Y-axis. It’s essential for understanding the starting level of any linear model.