Excel FORECAST Function
last modified October 5, 2026
The FORECAST function predicts a future value along a straight
trend line fitted to known data. It uses simple linear regression, so it assumes
that the relationship between the two variables is linear. This tutorial
provides a comprehensive guide to using the FORECAST function with
detailed examples. You'll learn the basic syntax, the regression formula behind
it, practical applications, and important limitations.
FORECAST Function Basics
The FORECAST function calculates a y-value for a given x-value by
fitting a straight line to a set of known x-y pairs. It is useful for sales
forecasting, inventory planning, and any situation where a trend continues at a
roughly constant rate.
| Component | Description |
|---|---|
| Function Name | FORECAST |
| Syntax | =FORECAST(x, known_ys, known_xs) |
| Arguments | x, known_ys, known_xs (all required) |
| Return Value | Predicted y-value for the given x |
This table breaks down the essential components of the FORECAST
function. It shows the function name, the syntax format, the required
arguments, and the value it returns.
| Argument | Meaning |
|---|---|
| x | The independent x-value for which you want a prediction. It can be a number, a cell reference, or an expression that returns a number. |
| known_ys | The dependent array or range of y-values. This is the data you are trying to predict, and it comes first in the argument list. |
| known_xs | The independent array or range of x-values. It must contain the same number of data points as known_ys. |
The argument order is the most common source of mistakes. The dependent
y-values come first and the independent x-values come second, which is the
opposite of the order used by many chart tools. If known_ys and
known_xs are not the same size, the function returns the
#N/A error. If the x-values have no variance (they are all the
same), the function returns the #DIV/0! error.
The Regression Formula
FORECAST fits an ordinary least-squares line,
y = intercept + slope * x. The slope is the covariance of the two
ranges divided by the variance of the x-values, and the intercept places the
line through the means of both ranges. The three formulas below define the
calculation exactly.
slope = COVARIANCE.P(known_ys, known_xs) / VAR.P(known_xs) intercept = AVERAGE(known_ys) - slope * AVERAGE(known_xs) FORECAST(x, known_ys, known_xs) = intercept + slope * x
Because the same number of data points is used for both the covariance and the
variance, the sample and population versions of those functions give the same
slope. Excel's SLOPE and INTERCEPT functions return
these two values directly, so FORECAST(x, ys, xs) is equivalent to
INTERCEPT(ys, xs) + SLOPE(ys, xs) * x.
Basic FORECAST Example
This example demonstrates the simplest use of the FORECAST function with a small dataset. We'll predict the next value in a linear sequence.
| A (Month) | B (Sales) | C |
|---|---|---|
| 1 | 100 | |
| 2 | 150 | |
| 3 | 200 | |
| 4 | =FORECAST(A4,B1:B3,A1:A3) |
The table shows monthly sales data for 3 months. We'll forecast sales for month 4 based on the linear trend of months 1-3. The FORECAST formula appears in C4.
=FORECAST(A4,B1:B3,A1:A3)
This formula predicts the sales value for month 4 (x=4) based on known months 1-3 (A1:A3) and their sales (B1:B3). The result will be 250, continuing the +50 per month trend. This shows basic linear forecasting.
FORECAST Worked Example
This example walks through the calculation with data that does not fall on a perfect line, which is the realistic case. The x-values are 1 through 5 and the y-values are 1, 3, 2, 5, and 4.
| A (x) | B (y) | C |
|---|---|---|
| 1 | 1 | |
| 2 | 3 | |
| 3 | 2 | |
| 4 | 5 | |
| 5 | 4 | |
| 6 | =FORECAST(A6,B1:B5,A1:A5) |
The table holds five known pairs and forecasts a sixth value. The formula predicts y for x = 6 from the y-range B1:B5 and the x-range A1:A5.
=FORECAST(A6,B1:B5,A1:A5)
The mean of x is 3 and the mean of y is 3. The covariance is 1.6 and the variance of x is 2.0, so the slope is 1.6 / 2.0 = 0.8. The intercept is 3 - 0.8 * 3 = 0.6. The prediction for x = 6 is therefore 0.6 + 0.8 * 6 = 5.4. Because x = 3 equals the mean of the x-values, the forecast there is exactly the mean of y, which is 3.
FORECAST with a Larger Dataset
This example uses a larger dataset to demonstrate how FORECAST works with more data points. We'll predict monthly revenue from 12 months of history that grows steadily by 500 per month from 12,000 to 17,500.
| A (Month) | B (Revenue) | C |
|---|---|---|
| 1 | 12000 | |
| 2 | 12500 | |
| ... | ... | |
| 12 | 17500 | |
| 13 | =FORECAST(A13,B1:B12,A1:A12) |
The table represents 12 months of revenue data (simplified here). We're forecasting revenue for month 13 based on the full year's trend.
=FORECAST(A13,B1:B12,A1:A12)
This formula predicts revenue for month 13 based on the linear trend of months 1-12. The slope is 500 per month, so the result is 18,000. More data points generally improve forecast accuracy by better defining the trend line.
FORECAST with Non-Sequential X Values
FORECAST can work with non-sequential x-values. This example shows temperature predictions at irregular time intervals.
| A (Hour) | B (Temp °F) | C |
|---|---|---|
| 6 | 68 | |
| 9 | 72 | |
| 12 | 78 | |
| 15 | =FORECAST(15,B1:B3,A1:A3) |
The table shows temperature readings at 6AM, 9AM, and noon. We'll predict the temperature at 3PM (hour 15) using these irregular measurements.
=FORECAST(15,B1:B3,A1:A3)
This formula predicts temperature at hour 15 based on measurements at hours 6, 9, and 12. The fitted line rises by about 1.67 degrees per hour, so the result is about 82.7°F. FORECAST works with any numeric x-values, not just regular sequences.
FORECAST with Named Ranges
Using named ranges makes FORECAST formulas more readable. This example demonstrates forecasting with named ranges for better spreadsheet organization.
| A (Week) | B (Units Sold) | C |
|---|---|---|
| 1 | 150 | |
| 2 | 170 | |
| 3 | 190 | |
| 4 | =FORECAST(A4,Sales,Weeks) |
The table assumes cells B1:B3 are named "Sales" and A1:A3 are named "Weeks". We're forecasting sales for week 4 using these named ranges in the formula.
=FORECAST(A4,Sales,Weeks)
This formula predicts units sold in week 4 based on weeks 1-3 data. With named ranges, the formula becomes more readable and maintainable. The result would be 210, continuing the +20 units/week trend.
FORECAST vs. FORECAST.LINEAR vs. TREND
Starting with Excel 2016, FORECAST was replaced by
FORECAST.LINEAR. The two functions take the same arguments and
return identical results; FORECAST remains available for backward
compatibility, but Microsoft recommends the newer name. TREND
performs the same regression but returns an array, so it can predict several
x-values at once.
| Function | Syntax | Returns |
|---|---|---|
| FORECAST | =FORECAST(x, known_ys, known_xs) | One predicted y-value |
| FORECAST.LINEAR | =FORECAST.LINEAR(x, known_ys, known_xs) | One predicted y-value, identical to FORECAST |
| TREND | =TREND(known_ys, known_xs, new_xs) | One value or an array of predicted y-values |
The next table shows annual revenue with FORECAST, FORECAST.LINEAR, and TREND all predicting the 2021 value.
| A (Year) | B (Revenue) | C (FORECAST) | D (TREND) |
|---|---|---|---|
| 2018 | 50000 | ||
| 2019 | 55000 | ||
| 2020 | 60000 | ||
| 2021 | =FORECAST(A4,B1:B3,A1:A3) | =TREND(B1:B3,A1:A3,A4) |
=FORECAST(A4,B1:B3,A1:A3) ' 65000 =FORECAST.LINEAR(A4,B1:B3,A1:A3) ' 65000 =TREND(B1:B3,A1:A3,A4) ' 65000
All three formulas return 65,000 for 2021 in this case. FORECAST and
FORECAST.LINEAR are interchangeable and are the simplest choice for a single
prediction. TREND uses the same calculation method but is more flexible: pass a
range of new x-values and it spills an array of predictions. In Excel 2019 and
earlier, entering TREND over several cells requires Ctrl+Shift+Enter.
Note that TREND lists the y-range before the x-range, matching the FORECAST
argument order.
FORECAST and Related Functions
Several functions expose the pieces of the same regression line.
SLOPE returns the slope, INTERCEPT returns the
intercept, and RSQ returns the square of the correlation
coefficient, which is the fraction of the variation in y explained by x. The
next example uses x-values 1 to 5 and y-values 1, 3, 2, 5, 4.
| A (x) | B (y) |
|---|---|
| 1 | 1 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 4 |
=FORECAST(6,B1:B5,A1:A5) ' 5.4 =SLOPE(B1:B5,A1:A5) ' 0.8 =INTERCEPT(B1:B5,A1:A5) ' 0.6 =RSQ(B1:B5,A1:A5) ' 0.64
The slope is 0.8, so y rises by about 0.8 for each one-unit rise in x. The
intercept of 0.6 is the value the line would give at x = 0. Together they
produce the forecast of 5.4 at x = 6. RSQ is 0.64, meaning the
linear trend explains 64 percent of the variation in y; the remaining 36
percent is scatter around the line. Note that SLOPE,
INTERCEPT, and RSQ all expect the y-range first, just
like FORECAST.
Limitations of FORECAST
The FORECAST function has several important limitations to
consider. It assumes a linear relationship between variables, which may not
always be accurate. The function is sensitive to outliers that can skew
results. It also requires that known_ys and known_xs
are the same size.
For non-linear trends, consider GROWTH for exponential growth or
LINEST and TREND for more control over the regression.
Always validate forecasts with actual data when possible. Remember that
forecasts become less reliable the further they extend beyond the known data
range.
FORECAST Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| known_ys and known_xs have a different number of cells, or one is empty | #N/A error | Make both ranges the same size and non-empty |
| All known_xs values are identical (zero variance) | #DIV/0! error | Include x-values that actually vary |
| x is non-numeric text | #VALUE! error | Pass a number or a cell that contains a number |
| A range contains text, logical values, or blanks | Wrong result or an error | Use only numeric cells, or clean the data first |
| Only one x-y pair is supplied | #DIV/0! error | Supply at least two numeric pairs |
| A referenced cell already holds an error | That same error is returned | Fix the source error first |
The FORECAST function turns a set of known x-y pairs into a single,
quantitative prediction. It fits a straight line by least squares and evaluates
that line at the x-value you supply, which makes it a fast and dependable tool
for sales, inventory, and trend projections. Its power comes with two
obligations: keep the two ranges the same size and make sure the x-values vary,
and never forget that the result is only as trustworthy as the linear
assumption behind it. Plot the data, watch for outliers, prefer
FORECAST.LINEAR in new workbooks, and use TREND when
you need many predictions at once. Used with that care, FORECAST
remains one of the most practical statistical functions in Excel.
Author
List all Excel Formulas.