They work best for linear datasets and in situations when you want to forecast a general trend ignoring insignificant data fluctuations.Īs an example, we will try to predict our web-site traffic for the next 7 days based on the data for the previous 3 weeks.
How to use FORECAST function in Excel - formula exampleĪs already mentioned, the Excel FORECAST and FORECAST.LINEAR functions are purposed for linear trend forecasting. If the variance of known_x's is zero, the #DIV/0! error occurs.If the x value is non-numeric, the formula returns the #VALUE! error.If the known_x's and known_y's ranges are of different lengths or empty, the #N/A! error occurs.If your FORECAST formula returns an error, this is most likely because of the following reasons: The values of x̄ and ȳ are the sample means (averages) of the known x-values and y-values. How FORECAST and FORECAST.LINEAR calculate future valuesīoth functions calculate a future y-value by using the linear regression equation:Īnd the b coefficient (slope of the line) is:
This function is available in Excel for Office 365, Excel 2019, and Excel 2016. The syntax of the FORECAST function is as follows:
In other words, FORECAST projects a future value along a line of best fit based on historical data. The FORECAST function in Excel is used to predict a future value by using linear regression.
The main focus of this tutorial will be on the two main forecasting functions, but we will touch briefly on other functions as well to help you understand their purpose and basic uses. In Microsoft Excel, there are several functions that can help you create linear and exponential smoothing forecasts based on historical data such as sales, budgets, cash flows, stock prices, and the like. The tutorial explains how to use Excel FORECAST and other related functions with formula examples.