This article describes the formula syntax and usage of the FORECAST.LINEAR and FORECAST functions in Microsoft Excel.
Note: In Excel 2016, the FORECAST function was replaced with FORECAST.LINEAR as part of the new Forecasting functions. The syntax and usage of the two functions are the same, but the older FORECAST function will eventually be deprecated. It's still available for backward compatibility, but consider using the new FORECAST.LINEAR function instead.
Description
Calculate, or predict, a future value by using existing values. The future value is a y-value for a given x-value. The existing values are known x-values and y-values, and the future value is predicted by using linear regression. You can use these functions to predict future sales, inventory requirements, or consumer trends.
Syntax
FORECAST.LINEAR(x, known_y's, known_x's)
- or -
FORECAST(x, known_y's, known_x's)
The FORECAST/FORECAST.LINEAR function syntax has the following arguments:
|
Argument |
Required |
Refers to |
|
x |
yes |
The data point for which you want to predict a value. |
|
known_y's |
yes |
The dependent array or range of data. |
|
known_x's |
yes |
The independent array or range of data. |
Remarks
-
If x is nonnumeric, FORECAST and FORECAST.LINEAR return the #VALUE! error value.
-
If known_y's or known_x's is empty or one has more data points than the other, FORECAST and FORECAST.LINEAR return the #N/A error value.
-
If the variance of known_x's equals zero, then FORECAST and FORECAST.LINEAR return the #DIV/0! error value.
-
The equation for FORECAST and FORECAST.LINEAR is a+bx, where:
and:
and where x and y are the sample means AVERAGE(known_x's) and AVERAGE(known y's).
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
|
Known Y |
Known X |
|
|
6 |
20 |
|
|
7 |
28 |
|
|
9 |
31 |
|
|
15 |
38 |
|
|
21 |
40 |
|
|
Formula |
Description |
Result |
|
=FORECAST.LINEAR(30,A2:A6,B2:B6) |
Predicts a value for y given an x value of 30 |
10.607253 |
