Excel functions / Statistics
FORECAST function
Predicts a y value for a new x from the best-fit straight line through known data.
Syntax
=FORECAST(x, known_y, known_x)
Arguments
x: The x value to predict y for.known_y: The dependent values: what you want to explain or predict.known_x: The independent values, one for each known_y.
When to use it
FORECAST predicts a y value for a new x from the best-fit straight line through your known data. In one function it does INTERCEPT + SLOPE × x.
The arguments are the new x first, then the known y values, then the known x values. Excel 2016 and later also call it FORECAST.LINEAR; both names work.
Examples
Sales for a planned ad spend
A B C D 1 Ad spend Sales 2 10 120 276.6 3 15 150 4 20 168 5 25 205 6 30 222 In D2:
=FORECAST(40, B2:B6, A2:A6)→ 276.6Next month from a monthly series
A B C 1 Month Revenue 2 1 42 3 2 45 4 3 44 5 4 49 6 5 52 7 6 50 8 9 7 53.6 10 8 In C9:
=FORECAST(A9, $B$2:$B$7, $A$2:$A$7)→ 53.6Lock the known ranges with $ before copying it down for more months.
Common mistakes
- Mixing up the argument order. FORECAST(x, known_y, known_x): the new x comes first, and y comes before x.
- Forecasting far ahead. A straight line assumes the next values follow the same trend. The further from the data, the less reliable the forecast.
- Unlocked ranges when copying. Without $, the known ranges slide down as you fill, and each forecast uses different data.
FORECAST in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: FORECAST
- Danish: PROGNOSE
- Dutch: VOORSPELLEN
- Finnish: ENNUSTE
- French: PREVISION
- German: SCHÄTZER
- Hungarian: ELŐREJELZÉS
- Italian: PREVISIONE
- Norwegian: PROGNOSE
- Polish: REGLINX
- Portuguese (Brazil): PREVISÃO
- Portuguese (Portugal): PREVISÃO
- Russian: ПРЕДСКАЗ
- Spanish: PRONOSTICO
- Swedish: PREDIKTION
- Turkish: TAHMİN
Also written as FORECAST.LINEAR.
Try FORECAST in the playground
Practise it
- Linear regression: SLOPE, INTERCEPT and LINEST
- Forecasting a trend with TREND
- Trendlines and forecasts
Related functions
- TREND: Values on the best-fit straight line (least squares): fitted values for the known data, or predictions for new x values. Spills.
- SLOPE: The slope of the straight line that best fits the data: how much y changes for each 1 added to x.
- INTERCEPT: Where the best-fit straight line crosses the y axis: the predicted y when x is 0.
- LINEST: Fits a straight line by least squares and returns its slope(s) and intercept, with the full regression statistics (standard errors, R², F, sums of squares) if you ask. Spills.