Forecasting a trend with TREND
Draw a trend line through monthly revenue, forecast the next three months, and see which months beat the trend. Advanced · 6 min · TREND, FORECAST, Residuals, COUNTIF
Revenue goes up and down from month to month, but is there a trend underneath? TREND fits the same straight line as SLOPE and INTERCEPT, then hands back the values on that line:
=TREND(known_y, known_x)gives the fitted value for every month you already have. It spills a whole column.=TREND(known_y, known_x, new_x)extends the line to new x values: a forecast.
Here x is just the month number, 1 to 12. The gap between what actually happened and the trend is called the residual.
Steps
In
D2, fit the trend line through the year:=TREND(C2:C13, A2:A13). Revenue is y, the month number is x, and the result spills down toD13.Extend the line into next year. In
D15, add the new month numbers as a third argument:=TREND(C2:C13, A2:A13, A15:A17)Which months beat the trend? In
E2, subtract the trend from the actual revenue,=C2-D2, and fill it down toE13. Positive means above the line.In
H2, count the months that came in above the trend:=COUNTIF(E2:E13, ">0")
Remember
=TREND(y, x)spills the fitted values for your data, and=TREND(y, x, new_x)forecasts new x values.- For a single new value,
=FORECAST(new_x, y, x)does the same. - Residual = actual − trend. Look for runs of positive or negative residuals: they mean a straight line isn't the whole story.
- TREND also takes several x columns, for forecasts with more than one driver.
In the function reference: COUNTIF, TREND
Previous: Linear regression: SLOPE, INTERCEPT and LINEST · Next: Histograms with FREQUENCY