INTERCEPT Function (LibreOffice Calc)
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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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.