How to use FORECAST.LINEAR Function in Excel

The FORECAST.LINEAR 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.

Syntax:= FORECAST.LINEAR(x, known_y’s, known_x’s)

The FORECAST.LINEAR function syntax has the following arguments:

1. X (required argument) – This is a numeric x-value for which we want to forecast a new y-value.
2. Known_y’s (required argument) – The dependent array or range of data.
3. Known_x’s (required argument) – This is the independent array or range of data that is known to us.

Example: Let’s look at some Excel FORECAST.LINEAR function examples and explore how to use the FORECAST.LINEAR function as a worksheet function in Microsoft Excel:

Let’s take some sales data for 2019 that we’d like to forecast for 12 months of 2020. In a separate column I’ll insert my FORECAST.LINEAR function:

Syntax:  =FORECAST.LINEAR(B14,$C$2:$C$13,$B$2:$B$13)

Notice the ‘x’ argument cell reference is relative, so that when I copy the formula down it picks up the next date and so on. Meanwhile the known_x and known_y references are absolute.

Result: 8465.889516

Based on the Excel spreadsheet above, the following FORECAST.LINEAR examples would return:

Chart Forecast Data
Notice that I entered the forecast figures in a separate column. I did this so that I can colour the forecast line different to the actual sales. To do this I need it entered as a separate series in the chart:

Note:

1. The length of the known_x’s array should be the same as that of the known_y’s, and the variance of the known_x’s must not be zero.
2. The FORECAST.LINEAR function was introduced in Excel 2016 (to replace the old FORECAST function). The FORECAST.LINEAR function is no longer available in earlier versions of Excel. It is also not available in Excel 2016 for Mac.
3. #N/A error – Occurs if the supplied known_x’s and the supplied known_y’s arrays are of different lengths.
4. #DIV/0! error – Occurs if the variance of the supplied known_x’s is equal to zero.
5. #VALUE! error – Occurs if the given future value of x is non-numeric.
6. 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).