FORECAST.LINEAR in Excel: forecasting along a linear trend

The syntax

=FORECAST.LINEAR(x, known_y's, known_x's) has three arguments. x is the point you want a value for, for example month 11 or a date. known_y's are the values you want to predict, such as revenue, and known_x's the matching periods. Note the order: the y range comes first, the x range second, which is the reverse of how most people think about a chart. Both ranges must have the same number of values, or the function returns #N/A.

FORECAST.LINEAR replaced the older FORECAST function; both take the same arguments and return the same result, so older files keep working. The x values do not have to be 1, 2, 3: real dates work too, because Excel stores dates as day numbers. In that case the slope is per day, and the forecast for a future date is just =FORECAST.LINEAR(DATE(2027,1,31),B2:B11,A2:A11). Evenly spaced periods make the slope easier to read, though.

Step by step: what the function calculates

A linear trend is the line y = intercept + slope × x that makes the sum of squared vertical distances to the data points as small as possible. Excel exposes both parts separately: =SLOPE(B2:B11,A2:A11) returns 0.853, the average increase per month, and =INTERCEPT(B2:B11,A2:A11) returns 17.4, the line's value at month 0. Month 11 by hand is 17.4 + 0.853 × 11 = 26.78, exactly what FORECAST.LINEAR returns.

Filling =FORECAST.LINEAR(A2,$B$2:$B$11,$A$2:$A$11) down column C gives the fitted value for every past month, with the data ranges locked by $ and the x moving. The residuals in column D, actual minus fitted, show where the line misses: the largest is −0.97k in month 9. Residuals of a least-squares line always add up to zero. If they show a pattern, for example negative at both ends and positive in the middle, the data are curved and a straight line is the wrong model.

The TREND function gives the same value: =TREND(B2:B11,A2:A11,11) returns 26.78. TREND can also return several forecasts at once when you pass a range of new x values, and it accepts several x columns for multiple regression, which is covered in the LINEST guide.

How reliable is the forecast?

Two numbers tell you how well the line fits. R², from =RSQ(B2:B11,A2:A11), is the share of the variation in revenue that the trend explains: 0.945 means 94.5%. It is the same R² that a chart trendline displays when you tick "Display R-squared value on chart". STEYX, from =STEYX(B2:B11,A2:A11), is the standard error of the estimate: a typical distance between the actual points and the line, here 0.66k, in the unit of the data.

A rough range for a forecast is the forecast ± 2 × STEYX, which gives 25.46k to 28.10k for month 11. Treat that as a minimum: the exact prediction interval is slightly wider, and it widens further the farther you extrapolate beyond the last known x. A high R² also does not prove the trend will continue. It only says the past data were close to a straight line; a price change, a new competitor or a season can break it.

Worked example: a ten-month revenue trend forecast three months ahead

Monthly revenue in thousands (yellow). Column C is the fitted trendline, D the residual; rows 12 to 14 forecast months 11 to 13. The lower block rebuilds the forecast from SLOPE and INTERCEPT and measures the fit.

Worked example: a ten-month revenue trend forecast three months ahead

	A	B	C	D
1	Month no.	Revenue (k)	Trend value	Residual
2	1	18.2	18.25	-0.05
3	2	19.5	19.11	0.39
4	3	19.1	19.96	-0.86
5	4	21.0	20.81	0.19
6	5	22.4	21.66	0.74
7	6	21.8	22.52	-0.72
8	7	23.9	23.37	0.53
9	8	24.6	24.22	0.38
10	9	24.1	25.07	-0.97
11	10	26.3	25.93	0.37
12	11	forecast	26.78
13	12	forecast	27.63
14	13	forecast	28.49
15
16	Trendline statistics			Value
17	Slope per month (SLOPE)			0.853
18	Intercept at month 0 (INTERCEPT)			17.400
19	Month 11 by hand = intercept + slope × 11			26.78
20	Month 11 with TREND			26.78
21	R² (RSQ)			0.945
22	Standard error of the estimate (STEYX)			0.661
23	Rough range for month 11: low = forecast − 2 × STEYX			25.46
24	Rough range for month 11: high = forecast + 2 × STEYX			28.10
25	Sum of residuals (always about 0)			0.000

Illustrative data. A trendline describes past data; it is not a guarantee of future results.

FORECAST.LINEAR, chart trendline or moving average?

A chart trendline (right-click the series, Add Trendline, Linear) draws exactly the same line and can show its equation, here y = 0.8527x + 17.4. It is the quickest visual check, but the numbers stay on the chart. FORECAST.LINEAR puts them into cells where you can use them in a budget or compare them with actuals later.

Use a linear forecast when the data rise or fall by a roughly constant amount per period and there is no strong seasonal pattern. For a level that wanders without a clear direction, a moving average is often more accurate. For growth by a constant percentage, such as compounding user numbers, a straight line underestimates the later periods; GROWTH fits an exponential curve instead. And for seasonal data, remove the season first or forecast each month from the same month in earlier years.

Common mistakes

• Swapping the y and x ranges. FORECAST.LINEAR expects known_y's first. Reversed ranges give a forecast of the month number from the revenue, which is meaningless.
• Forgetting the $ signs. Without $B$2:$B$11 the data ranges shift as you fill down and each row uses a different line.
• Extrapolating too far. A line fitted on 10 months says little about month 30. The uncertainty grows with the distance from the data.
• Fitting a line to curved or seasonal data. Look at the residuals or a chart first. A pattern in the residuals means the straight line is the wrong model.
• Treating R² as forecast accuracy. R² measures the fit to past data. Check the forecast against actuals as they arrive.
• Including the forecast rows in the known data. If the known_y range runs into rows with text or forecasts, the ranges no longer match or the line is fitted partly to its own output.