Forecast function

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

See Also

Forecasting functions (reference)