Related Function:

The FORECAST function calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. 

  • The FORECAST function has been replaced in Excel 2016 with the FORECAST.LINEAR function.

Syntax

=FORECAST(x,known_y’s,known_x’s)

Arguments

Argument Description
x The data point for which you want to predict a value
known_y’s The dependent array or range of data
known_x’s The independent array or range of data

Examples

  A B C D E F
1 Known Y Known X   Formula Result Notes
2 8 17   =FORECAST(30,A2:A5,B2:B5) 1.25 Predicts a value for y given an x value of 30
3 1 19        
4 4 11        
5 7 13        

Usage note: use this function to predict future sales, inventory requirements, or consumer trends.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if the supplied future value of x is non-numeric
#N/A Occurs the supplied known_x’s or known_y’s arrays are empty or are of different lengths
#DIV/0! Occurs if the variance of the supplied known_x’s evaluates to zero

The FORECAST function calculates a new y-value using the simple straight lin equation for FORECAST is a + bx where    

    \[    a = \bar{y} - b \bar{x}    \]

and

    \[    b = \frac {\sum (x - \bar{x})(y - \bar{y})} {\sum (x - \bar{x})^2}    \]

and the values of \bar{x} and \bar{y} are the sample means (averages) of the known x- and known y-values.