Home » Excel-Built-In-Functions » Excel-Statistical-Functions » Excel-Forecast-Function

Excel FORECAST Function

Forecast Equations

The Excel Forecast Function calculates a new y-value using the simple straight line equation:

Straight Line Equation
where,
Equation for the intercept of a linear regression line
and
Equation for the slope of a linear regression line
and the values of   x   and   y   are the sample means (the averages) of the known x- and y-values.
Related Function:
TREND function

Function Description

The Excel Forecast function predicts a future point on a linear trend line fitted to a supplied set of x- and y- values.

The syntax of the function is:

FORECAST( x, known_y's, known_x's )

Where the arguments are as follows:

x - A numeric x-value for which you want to forecast a new y-value.
known_y's - An array of known y-values.
known_x's -

An array of known x-values.

Note that the length of the known_x's array should be the same length as known_y's, and the variance of the known_x's must not be zero.

Note that, in Excel 2016 the Forecast function has been replaced by the Forecast.Linear function. However, the Forecast function is still available in Excel 2016, to ensure compatibility with earlier versions of Excel.


Forecast Function Example

In the spreadsheet below, the Excel Forecast function is used to predict an additional point along the straight line of best fit through a set of known x- and y-values (stored in cells F2:F7 and G2:G7).

Example of use of the Excel Forecast Function

As shown in cell F7 of the spreadsheet, the function to calculate the forecast y-value at x=7 is:

=FORECAST( 7, G2:G7, F2:F7 )

This gives the result 32.666667.

For further examples of the Excel Forecast function, see the Microsoft Office website


Forecast Function Errors

If you get an error from the Forecast function, this is likely to be one of the following:

Common Errors
#N/A -

Occurs if either:

  • The known_x's and known_y's arrays are of different lengths
or
  • One or both of the known_x's or the known_y's arrays is empty.
#DIV/0! - Occurs if the variance of the supplied known_x's is equal to zero.
#VALUE! - Occurs if the supplied future value of x is non-numeric.
Return to the Excel Statistical Functions Page
Return to the List of All Built-In Excel Functions