TREND Function (LibreOffice Calc)
The TREND function in LibreOffice Calc returns predicted Y-values based on linear regression. This guide explains syntax, forecasting behavior, examples, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the TREND Function Does â–¾
- Predicts Y-values based on a linear regression model
- Works with one or more independent variables
- Supports forecasting future values
- Works as an array function (spills in modern Calc)
- Can fill missing values or extend a trendline
- Works across sheets
TREND is the easiest way to generate regression-based predictions.
Syntax â–¾
TREND(known_y; known_x; new_x; const)
Where:
known_y— dependent variable (Y values)known_x— independent variable(s) (X values)new_x— X values to predict forconst— TRUE = calculate intercept; FALSE = force intercept = 0
If
new_x is omitted, TREND predicts Y-values for the same X-values in known_x.
Basic Examples â–¾
Predict Y-values for the same X-range
=TREND(B1:B10; A1:A10)
Forecast future values
Predict Y for X = 11 to 20:
=TREND(B1:B10; A1:A10; A11:A20)
Forecast with intercept forced to zero
=TREND(B1:B10; A1:A10; A11:A20; FALSE)
Forecast across sheets
=TREND(Sheet1.B1:B50; Sheet2.A1:A50; Sheet2.A51:A60)
Advanced Examples â–¾
Fill missing values in a dataset
=TREND(B1:B100; A1:A100; A1:A100)
Multi-variable regression prediction
=TREND(Y1:Y20; X1:Z20; X21:Z30)
Predict using LINEST coefficients (manual equivalent)
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)
TREND automates this.
TREND ignoring errors
=TREND(IF(ISNUMBER(B1:B10); B1:B10); A1:A10; A11:A20)
(Confirm with Ctrl+Shift+Enter in older Calc.)
TREND on visible cells only (filtered data)
Use SUBTOTAL helper column to filter X/Y before passing to TREND.
TREND for time-series forecasting
If A1:A10 contains dates:
=TREND(B1:B10; A1:A10; A11:A20)
TREND for filling gaps in irregular data
=TREND(B$1:B$100; A$1:A$100; A2)
How TREND Calculates Predictions â–¾
TREND uses the same regression model as LINEST:
y = m*x + b
Steps:
- Compute slope (m) and intercept (b) using least squares
- For each
new_x, compute:
y = m * new_x + b - Return an array of predicted values
If const = FALSE, the model becomes:
y = m*x
Common Errors and Fixes â–¾
Err:502 — Invalid argument
Occurs when:
- X and Y ranges have mismatched lengths
- Non-numeric text included
new_xcontains invalid values
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range references malformed
TREND returns only one value instead of many
Occurs when:
- Output range too small in older Calc
- Not entered as array formula (older versions)
TREND predictions look incorrect
Possible causes:
- Relationship is not linear
- Outliers distort regression
- X-values not sorted (sorting not required but helps interpretation)
Best Practices â–¾
- Use TREND for forecasting and filling missing values
- Use LINEST when you need full regression statistics
- Use GROWTH for exponential forecasting
- Remove outliers before modeling
- Use named ranges for cleaner formulas
- Always check scatter plots to confirm linearity
TREND is the fastest way to generate regression-based forecasts — perfect for projections, planning, and filling gaps in your data.