Course / Data Analysis

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:

Here x is just the month number, 1 to 12. The gap between what actually happened and the trend is called the residual.

Steps

  1. 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 to D13.

  2. Extend the line into next year. In D15, add the new month numbers as a third argument: =TREND(C2:C13, A2:A13, A15:A17)

  3. Which months beat the trend? In E2, subtract the trend from the actual revenue, =C2-D2, and fill it down to E13. Positive means above the line.

  4. In H2, count the months that came in above the trend: =COUNTIF(E2:E13, ">0")

Remember

In the function reference: COUNTIF, TREND

Previous: Linear regression: SLOPE, INTERCEPT and LINEST · Next: Histograms with FREQUENCY