The following time series forecasting functions can be used to predict future values based on historical data. These functions use advanced machine learning algorithms, such as Exponential Triple Smoothing (ETS).
- These functions were introduced in Excel 2016 and so are not available in earlier versions.
The FORECAST.ETS function calculates or predicts a future value based on historical values by using the AAA version of the Exponential Smoothing (ETS) algorithm. The predicted value is a continuation of the historical values in the specified target date, which should be a continuation of the timeline. You can use this function to predict future sales, inventory requirements, or consumer trends.
|target_date||The data point for which you want to predict a value|
|values||The historical values for which you want to forecast the next points|
The independent array or range of numeric data
Optional. A numeric value specifying the type of seasonality
Optional. The timeline requires a constant step between data points, however, FORECAST.ETS will automatically adjust for up to 30% missing data
Optional. The timeline requires a constant step between data points, however, FORECAST.ETS will aggregate multiple points which have the same time stamp. The aggregation parameter is a numeric value indicating which method will be used to aggregate several values with the same time stamp
The FORECAST.ETS.SEASONALITY function returns the length of the repetitive pattern Excel detects for the specified time series. FORECAST.ETS.Seasonality can be used following FORECAST.ETS to identify which automatic seasonality was detected and used in FORECAST.ETS. While it can also be used independently of FORECAST.ETS, the functions are tied since the seasonality detected in this function is identical to the one used by FORECAST.ETS, considering the same input parameters that affect data completion.
Note: All arguments for the FORECAST.ETS.SEASONALITY follow the same semantics as FORECAST.ETS.
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. You can use this function to predict future sales, inventory requirements, or consumer trends.
|x||The data point for which you want to predict a value|
|known_y’s||The dependent array or range of data|
|known_x’s||The independent array or range of data|
The FORECAST.ETS.CONFINT returns a confidence interval for the forecast value at the specified target date. A confidence interval of 95% means that 95% of future points are expected to fall within this radius from the result FORECAST.ETS forecasted (with normal distribution). Using confidence interval can help grasp the accuracy of the predicted model. A smaller interval would imply more confidence in the prediction for this specific point.
Note: All arguments for the FORECAST.ETS.CONFINT follow the same semantics as FORECAST.ETS with one additional argument:
|[confidence_level]||Optional. A numerical value between 0 and 1 (exclusive), indicating a confidence level for the calculated confidence interval|
The FORECAST.ETS.STAT returns a statistical value as a result of time series forecasting.
All arguments for the FORECAST.ETS.CONFINT follow the same semantics as FORECAST.ETS with one additional argument:
|statistic_type||A numeric value between 1 and 8, indicating which statistic will be returned for the calculated forecast. The following optional statistics can be returned:
Common Function Error(s)
|Problem||What went wrong|
|#VALUE!||Occurs if the timeline contains duplicate values|
|#NUM!||Occurs if either:
|#N/A||Occurs if the ranges of the timeline and values aren’t of same size|