Quick Navigation
TREND Function
Summary
The TREND function forecasts future values using linear regression analysis. It calculates the best-fitting straight line through known data points and predicts corresponding y-values for new x-values you specify.
Syntax
TREND(known_y's, [known_x's], [new_x's], [const])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| known_y's | Array |
Yes | The set of known dependent (y) values for regression analysis |
| known_x's | Array |
No | Optional set of known independent (x) variable values |
| new_x's | Array |
No | New x-values requiring predicted y-values |
| const | Boolean |
No | Logical value to include/exclude y-intercept (TRUE/FALSE) |
Using the TREND Function
TREND excels at predictive forecasting by applying least-squares linear regression to historical data. Enter as an array formula (Ctrl+Shift+Enter in older Excel versions) to generate multiple predictions across your specified range.
Common TREND Examples
Basic Sales Forecasting
=TREND(B2:B13,A2:A13,A14:A18)Predicts sales for next 5 months based on 12 months of historical sales data assuming months 1-12 as x-values.
Revenue Trend with Custom X-Values
=TREND(B2:B13,C2:D13,C14:D18)Forecasts revenue using two predictor variables (ad spend, website traffic) for future periods.
Force Through Origin
=TREND(B2:B10,A2:A10,A11:A15,FALSE)Predicts values forcing regression line through origin (no y-intercept).
Frequently Asked Questions
Common Errors and Solutions
#VALUE! error
Cause: known_y's and known_x's arrays have different dimensions
Solution: Ensure arrays match in size; use single column known_y's with multiple column known_x's
Single value returned
Cause: Not entered as array formula
Solution: Select output range first, then use Ctrl+Shift+Enter
#N/A in results
Cause: new_x's array larger than known data
Solution: Match new_x's dimensions to expected output range
Notes
- Uses least squares method identical to chart trendlines
- Perfect companion to LINEST for statistical validation
- Enable iterative calculation for best polynomial approximations
- Array constants use commas (rows) and semicolons (columns)
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in:
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+