The Excel Forecast.Linear Function

Linear Forecast Equations

The Excel Forecast.Linear Function calculates a new y-value using the simple straight line equation: where, and and the values of   x   and   y   are the sample means (the averages) of the known x- and the known y-values.
Related Function:
Forecast.Ets

Function Description

The Excel Forecast.Linear 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.LINEAR( x, known_y's, known_x's )

Where the function arguments are:

 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.

Notes:

• 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.
• The Forecast.Linear function was first introduced in Excel 2016 (to replace the old Forecast function). The Forecast.Linear function is therefore not available in earlier versions of Excel. It is also not available in Excel 2016 for Mac.

Excel Forecast.Linear Function Example

In the following spreadsheet, the Excel Forecast.Linear 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). As shown in cell F7 of the spreadsheet, the function to calculate the forecast y-value at x=7 is:

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

This gives the result 32.666667.

For further details of the Excel Forecast.Linear function, see the Microsoft Office website

Forecast.Linear Function Errors

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

Common Errors
 #N/A - Occurs if the supplied known_x's and the supplied known_y's arrays have different lengths. #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.