The FORECAST.LINEAR function in Excel returns a predicted value from a straight line fitted to known pairs of numbers.
It can estimate a missing point or extend an existing trend. The estimate is useful when a linear relationship reasonably describes the data.
In this article, I’ll show you how to forecast several future periods, estimate values between known points, and compare related forecasting functions.
FORECAST.LINEAR Function Syntax in Excel
The FORECAST.LINEAR function needs a target value and two matching sets of known values.
=FORECAST.LINEAR(x, known_ys, known_xs)
- x (required) is the data point for which you want Excel to predict a value.
- known_ys (required) is the dependent range containing the values you want to predict.
- known_xs (required) is the independent range containing the values that drive the prediction.
FORECAST.LINEAR fits the straight-line equation a + bx, where b is the slope and a is the intercept.
When to Use FORECAST.LINEAR Function
- Predict a future value when the known data follows a reasonably straight trend.
- Estimate a value between known points, such as drive time for a distance not listed in your table.
- Forecast several target values at once with a spilling formula in Excel 2021 and later.
- Work with dates as the independent values when the trend changes steadily over time.
- Check a forecast against TREND or the equivalent SLOPE and INTERCEPT calculation.
Example 1: Forecast Next Month’s Shipments
Let’s start with a forecast based on monthly shipping history.
Below is the dataset. Columns A and B contain ten month-end dates and shipped units, while D2 contains the next month-end date.
We want to estimate the units that will ship on November 30, 2026.
Here is the formula:
=FORECAST.LINEAR(D2,B2:B11,A2:A11)
The formula returns 1,691.7 in E2.
D2 supplies the date to forecast. B2:B11 contains the known shipment values, and A2:A11 contains their corresponding dates.
Excel stores dates as serial numbers, so the fitted line measures change per day. Uneven month lengths do not cause a problem.
Pro Tip: Make sure D2 contains a real Excel date. A date stored as text can make FORECAST.LINEAR return #VALUE!.
Example 2: Forecast Several Future Weeks at Once
Here’s how to return several weekly forecasts from one formula.
Below is the dataset. Columns A and B hold ten weeks of account-opening history, while D2:D5 contains four future week-ending dates.
We want one formula to forecast the new accounts opened for all four future weeks.
Here is the formula:
=FORECAST.LINEAR(D2:D5,$B$2:$B$11,$A$2:$A$11)
The single formula returns 73.3, 76.4, 79.4, and 82.5 in E2:E5.
Because x is D2:D5, Excel calculates one forecast for each future date and spills the results down column E.
The absolute references keep the historical dates and values fixed while Excel evaluates each date in the target range.
TREND is another good option when you mainly want several predictions: =TREND($B$2:$B$11,$A$2:$A$11,D2:D5).
Pro Tip: Keep E2:E5 empty before entering the formula. Any value in that output area blocks the spill and causes a #SPILL! error.
Example 3: Estimate Drive Times Between Known Distances
FORECAST.LINEAR also works with drivers that are not dates.
Below is the dataset. Columns A and B list delivery distances and drive times, while D2:D5 contains four distances to estimate.
We want to estimate the drive time for each distance in column D.
Here is the formula:
=FORECAST.LINEAR(D2:D5,$B$2:$B$9,$A$2:$A$9)
The spilled results are 21.6, 36.9, 52.2, and 65.3 minutes for 7, 14, 21, and 27 miles.
Excel fits one straight line through the known distances and times. It then reads the estimated time from that line for each new distance.
These target distances sit between known values, so this is interpolation. The same function can extrapolate, but distant targets are more sensitive to a poor fit.
If the relationship clearly curves, a straight-line forecast is the wrong model. GROWTH may suit an exponential pattern better.
Example 4: Compare FORECAST.LINEAR with FORECAST and TREND
Now let’s compare four ways to read the same fitted line.
Below is the dataset. Columns A and B contain store sizes and monthly rents, while G2 holds the 2,600-square-foot size to estimate.
We want to estimate the monthly rent with FORECAST.LINEAR, FORECAST, TREND, and a written-out slope-and-intercept calculation.
Here is the FORECAST.LINEAR formula:
=FORECAST.LINEAR($G$2,$B$2:$B$9,$A$2:$A$9)
The FORECAST.LINEAR formula returns $5,055.05 in E2.
Here is the FORECAST formula:
=FORECAST($G$2,$B$2:$B$9,$A$2:$A$9)
The compatibility alias also returns $5,055.05 in E3.
Here is the TREND formula:
=TREND($B$2:$B$9,$A$2:$A$9,$G$2)
TREND returns $5,055.05 in E4.
And here is the same calculation written with SLOPE and INTERCEPT:
=SLOPE($B$2:$B$9,$A$2:$A$9)*$G$2+INTERCEPT($B$2:$B$9,$A$2:$A$9)
The written-out calculation returns $5,055.05 in E5.
All four methods display the same value because they use the same straight-line relationship.
FORECAST.LINEAR is the name Microsoft ships for current workbooks. FORECAST remains as a compatibility alias so older workbooks keep opening and calculating.
Use FORECAST.LINEAR in Excel 2016 or later. Keep FORECAST when a workbook must open in Excel 2013 or earlier.
Pro Tip: The methods can differ in the last floating-point bit. Compare them at a sensible display precision, such as the two decimal places used here.
Example 5: Fix Common FORECAST.LINEAR Errors
Finally, let’s break the formula in three common ways so the error messages make sense.
Below is the dataset. Columns A:C contain eight weeks, a constant Store ID of 5, and the weekly order totals.
We want to compare a valid week-nine forecast with formulas that have mismatched, constant, or nonnumeric inputs.
Here is the valid formula:
=FORECAST.LINEAR(9,C2:C9,A2:A9)
The formula returns 175.5 in F2.
Now shorten known_xs by one row:
=FORECAST.LINEAR(9,C2:C9,A2:A8)
The formula returns #N/A in F3 because C2:C9 has eight values, while A2:A8 has seven.
Next, point known_xs at the Store ID column:
=FORECAST.LINEAR(9,C2:C9,B2:B9)
The formula returns #DIV/0! in F4 because every value in B2:B9 is 5. A constant x range has no variation for Excel to measure.
Finally, use text for the target x value:
=FORECAST.LINEAR("Nine",C2:C9,A2:A9)
The formula returns #VALUE! in F5 because “Nine” is text rather than a number.
These errors usually point to the inputs, not the forecasting method. Check the target value, range sizes, and variation in known_xs before changing the formula.
Tips & Common Mistakes
- FORECAST.LINEAR fits one straight line. Use FORECAST.ETS for seasonal time series or GROWTH for an exponential relationship.
- Keep
known_ysandknown_xsthe same size. Empty or mismatched ranges can return #N/A. - Dates must be real Excel dates, not text. Format the forecast cell as a number so Excel does not display the result as a date.
- A range supplied as
xspills in Excel 2021 and later. Blocked output cells cause #SPILL!, while@reduces the range to one value. - Excel 2019 and earlier do not spill results. Enter one forecast per row, or select the output range and confirm the array formula with Ctrl+Shift+Enter.
- Keep forecasts reasonably close to the known x range. The farther you extrapolate, the more a small trend mismatch can distort the estimate.
- Use IFERROR only after checking the source of an error. Hiding mismatched ranges or constant x values makes the worksheet harder to repair.
A straight-line forecast is only useful when the known data follows a straight line. Otherwise, even a precise-looking result can mislead you.
When the pattern is linear, FORECAST.LINEAR gives you a clean formula for one prediction or a spilled set of predictions.
Related Excel Functions / Articles:
