FORECAST.LINEAR function
FORECAST.LINEAR predicts a y value for a given x by extending the least-squares straight line through your data. It is the modern name for FORECAST and gives identical results.
Example
Read it as a sentence: use the pairs in F1:F5 and G1:G5 to draw a straight trend line, then predict the G value when F is 6, which gives 5.8.
Excel shows 5.8
F
G
1
1
2
2
2
4
3
3
5
4
4
4
5
5
5
Data
Syntax
FORECAST.LINEAR(x, known_y's, known_x's)
-
x(required) - The x value to predict for.
-
known_y's(required) - Known dependent values.
-
known_x's(required) - Known independent values.
Returns: The predicted y for x on a least-squares straight line.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=FORECAST.LINEAR(6,{2,4,5,4,5},{1,2,3,4,5}) | 5.8 | Array constants |
=FORECAST.LINEAR(6,Data!G1:G5,Data!F1:F5) | 5.8 | Working example (ranges) |
=FORECAST.LINEAR("6",Data!G1:G5,Data!F1:F5) | 5.8 | Text-number x |
=FORECAST.LINEAR(6,Data!I1:I5,Data!H1:H5) | 6.25 | Blank pairs dropped |
=FORECAST.LINEAR({6,7},Data!G1:G5,Data!F1:F5) | 5.8 | Array x (spills) |
=FORECAST.LINEAR(6,Data!G1:G5,Data!J1:J5) | #DIV/0! | Constant x |
=FORECAST.LINEAR(6,Data!G1:G5,Data!K1:K3) | #N/A | Different sizes |
=FORECAST.LINEAR("abc",Data!G1:G5,Data!F1:F5) | #VALUE! | Text x |
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
G
H
I
J
K
1
1
2
1
2
3
1
2
2
4
4
3
2
3
3
5
3
5
3
3
4
4
4
4
4
3
5
5
5
5
5
3
Data
Watch out for
- y values come before x values.
- Only suits roughly linear trends.
Where it works
- Excel
- FORECAST.LINEAR arrived in Excel 2016. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .