Excel functions / Statistics
TREND function
Values on the best-fit straight line (least squares): fitted values for the known data, or predictions for new x values. Spills.
Syntax
=TREND(known_y, [known_x], [new_x], [const])
Arguments
known_y: The dependent values: what you want to explain or predict.known_x(optional): The independent values; several columns for several variables. Default 1, 2, 3, …new_x(optional): The x values to predict for. Default: known_x.const(optional): FALSE forces the line through zero. Default TRUE.
When to use it
TREND fits a straight line through your data by least squares, then returns values on that line: the fitted value for every point you have, or forecasts for new x values. It spills the whole result at once.
Leave out new_x to get the trend line for your own data, or give new x values to forecast them. Unlike FORECAST, TREND can also use several x columns at once.
Examples
The trend line through six months
A B C 1 Month Revenue 2 1 42 42.3 3 2 45 44.2 4 3 44 46.1 5 4 49 47.9 6 5 52 49.8 7 6 50 51.7 8 9 7 10 8 In C2:
=TREND(B2:B7, A2:A7)→ 42.3; 44.2; 46.1; 47.9; 49.8; 51.7Forecast the next two months
A B C 1 Month Revenue 2 1 42 3 2 45 4 3 44 5 4 49 6 5 52 7 6 50 8 9 7 53.6 10 8 55.5 In C9:
=TREND(B2:B7, A2:A7, A9:A10)→ 53.6; 55.5Without x values
A B C 1 Month Revenue 2 1 42 42.3 3 2 45 44.2 4 3 44 46.1 5 4 49 47.9 6 5 52 49.8 7 6 50 51.7 8 9 7 10 8 In C2:
=TREND(B2:B7)→ 42.3; 44.2; 46.1; 47.9; 49.8; 51.7With no known_x, TREND uses 1, 2, 3 and so on, which suits evenly spaced periods.
Common mistakes
- #SPILL! TREND spills one value per row. Clear the cells below the formula so the results have room.
- Reading the trend as the forecast for every month. The trend is the underlying line. Actual months will sit above or below it; the gap is called the residual.
TREND in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: LINTREND
- Danish: TENDENS
- Dutch: TREND
- Finnish: SUUNTAUS
- French: TENDANCE
- German: TREND
- Hungarian: TREND
- Italian: TENDENZA
- Norwegian: TREND
- Polish: REGLINW
- Portuguese (Brazil): TENDÊNCIA
- Portuguese (Portugal): TENDÊNCIA
- Russian: ТЕНДЕНЦИЯ
- Spanish: TENDENCIA
- Swedish: TREND
- Turkish: EĞİLİM
Practise it
Related functions
- FORECAST: Predicts a y value for a new x from the best-fit straight line through known data.
- LINEST: Fits a straight line by least squares and returns its slope(s) and intercept, with the full regression statistics (standard errors, R², F, sums of squares) if you ask. Spills.
- SLOPE: The slope of the straight line that best fits the data: how much y changes for each 1 added to x.
- INTERCEPT: Where the best-fit straight line crosses the y axis: the predicted y when x is 0.