Excel FORECAST.LINEAR Function

The FORECAST.LINEAR function is a Statistical function that predicts a future value by using existing values along a linear trend (linear regression). You can use a forecasting function to predict future sales, inventory requirements, or consumer trends. In this guide, we’re going to show you how to use the FORECAST LINEAR function and go over some tips and error handling methods.

Supported versions

• Excel 2016 or newer

FORECAST.LINEAR Function Syntax

Arguments

FORECAST.LINEAR Function Examples

The FORECAST.LINEAR function predicts the value of data point x by given historical known_y's values and known_x's data points.

In our example, our historical data resides in the range B5:C19. See that the cell D20 has the FORECAST.LINEAR to predict the value based on B20.

A single forecasted value will not tell you much. The best way to review and compare the forecasted data is to forecast for more points and use a line chart to see the trend. All you need to do is to generate more data points and copy the formula. Just pay attention to absolute and relative references, since you will want to keep ranges of the historical data.

Note that the cell D19 has the same value as the last historical data just to connect lines in the graph.

Download Workbook

Remarks

• The equation for the FORECAST.LINEAR is
  
  where
  
  
  x = AVERAGE(known_x's)
  y = AVERAGE(known_y's)
• If x is not a number, the function returns #VALUE! error.
• If known_y's or known_x's is empty or not at same size, the function returns the #N/A error.
• The FORECAST.LINEAR is the updated version of the FORECAST function.

Excel Forecasting Functions