FORECAST Function (LibreOffice Calc)
The FORECAST function in LibreOffice Calc predicts a single Y-value using linear regression. This guide explains syntax, examples, regression logic, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the FORECAST Function Does â–¾
- Predicts a single Y-value using linear regression
- Uses the same model as TREND and LINEST
- Ideal for simple predictions and quick estimates
- Works with numeric X/Y pairs
- Works across sheets
FORECAST is essentially:
y = m*x + b
Where m and b come from least-squares regression.
Syntax â–¾
FORECAST(x; known_y; known_x)
Where:
x— the X-value to predict forknown_y— dependent variable (Y values)known_x— independent variable (X values)
X and Y ranges must be the same size.
Basic Examples â–¾
Predict Y for X = 11
=FORECAST(11; B1:B10; A1:A10)
Predict Y for a value stored in a cell
=FORECAST(D1; B1:B100; A1:A100)
Forecast across sheets
=FORECAST(2025; Sheet1.B1:B50; Sheet2.A1:A50)
Forecast using dates
=FORECAST(DATE(2026;1;1); Sales; Dates)
Advanced Examples â–¾
Manual equivalent using SLOPE and INTERCEPT
=SLOPE(B1:B10; A1:A10) * 11 + INTERCEPT(B1:B10; A1:A10)
Forecast ignoring errors
=FORECAST(11; IF(ISNUMBER(B1:B10); B1:B10); A1:A10)
(Confirm with Ctrl+Shift+Enter in older Calc.)
Forecast using filtered (visible) data only
Use SUBTOTAL helper column to filter X/Y before passing to FORECAST.
Forecast with outlier removal
=FORECAST(11; FILTER(B1:B100; B1:B100<1000); FILTER(A1:A100; B1:B100<1000))
Forecast for time-series with irregular spacing
=FORECAST(D2; B$1:B$100; A$1:A$100)
Forecast using named ranges
=FORECAST(NewX; KnownY; KnownX)
How FORECAST Calculates Predictions â–¾
FORECAST uses the same regression model as LINEST:
-
Compute slope (m):
m = COVAR(known_x; known_y) / VAR(known_x) -
Compute intercept (b):
b = AVERAGE(known_y) - m * AVERAGE(known_x) -
Predict:
y = m * x + b
FORECAST is simply a convenience wrapper around these calculations.
Common Errors and Fixes â–¾
Err:502 — Invalid argument
Occurs when:
- X and Y ranges have mismatched lengths
- Non-numeric text included
xis non-numeric
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range references malformed
FORECAST returns unexpected value
Possible causes:
- Relationship is not linear
- Outliers distort regression
- X-values not aligned with Y-values
FORECAST differs from TREND
TREND returns multiple predictions; FORECAST returns one.
Best Practices â–¾
- Use FORECAST for single-value predictions
- Use TREND for multi-value forecasting
- Use LINEST when you need full regression statistics
- Remove outliers before modeling
- Plot your data to confirm linearity
- Use named ranges for cleaner formulas
FORECAST is perfect when you need a quick, single prediction — no arrays, no complexity, just a clean linear estimate.