Unlock the Potential of FORECAST.ETS in Excel for Calculations and Future Value Predictions

In addition to the FORECAST.ETS function in Excel for calculations and future value predictions, readers can also explore other Excel functions such as HLOOKUP for effective data searching.

Understanding FORECAST.ETS Function with Illustrative Examples
 

Explore the Functionality of FORECAST.ETS in Excel for Calculations and Future Value Predictions

The Forecast.Ets function in Excel utilizes an exponential smoothing algorithm to predict future values on a timeline, based on a sequence of existing values.

The syntax of the FORECAST.ETS function in Excel is as follows:

FORECAST.ETS( target_date, values, timeline, [seasonality], [data completion], [aggregation] )

Within this context:
- target_date: The date/time you want to predict the value for. (Must be after the last date/time in the historical timeline).
- Values: An array of known historical values that you want to forecast the next timestamp.

This parameter must meet the following requirements:
+ The timeline array must have the same length as the values array.
+ The dates/times in the timeline must have a consistent step length, although:
- Up to 30% of the points may be missing and handled, according to the value of the [data completion] parameter.
- There may be duplicates in the timeline, corresponding values will be aggregated, as defined by the [aggregation] parameter.
+ Dates/times in the timeline can use any format.
- [seasonality]: Optional. This parameter indicates the algorithm to be used to detect seasonality in the data.
If provided, this parameter must be a positive integer in the range from 0 to 8784, i.e.,

- [data completion]: Optional. This parameter specifies how the algorithm will handle missing points in the timeline.
If provided, the [data completion] parameter can take the value 0 or 1, i.e.,

- [aggregation]: Optional. This parameter specifies how the algorithm consolidates values with the same timestamp.

If provided, this parameter can be any integer in the range from 1 to 7, meaning:

Note: The Forecast.Ets.Stat function was first introduced in Excel 2016, so the function is not available in earlier versions of Excel, and it is also not available in Excel 2016 for Mac.

Example of the Forecast.Ets function in Excel, calculating or predicting future values

The spreadsheet below (first table) represents monthly income from January 2015 to April 2017. The values are illustrated in the chart below:

The Excel function Forecast.Ets can be utilized to predict the income value for May 2017 as follows:

= FORECAST.ETS( A30, B2:B29, A2:A29 )

Executing the function yields a result of 1461.632054.

Note, in the example above:
- Target_date, i.e., May 2017 is stored in cell 30 in the example spreadsheet.
- Although the timeline array (stored in cells A2:A29 in the example spreadsheet) is arranged in chronological order, this is unnecessary for the Forecast.Ets function.
- The parameters [seasonality], [data completion], and [aggregation] are omitted, using default values, i.e., [seasonality] = 1; [data completion] = 1; [aggregation] = 0.

For more detailed information on the Excel Forecast.Ets function, readers can visit the official Microsoft Office page.

Common Errors with FORECAST.ETS Function in Excel, calculating or predicting future values

If encountering errors with the Forecast.Ets function in Excel, it could be one of the following errors:
- Error #N/A: This error occurs if the provided values and timeline arrays have different lengths.
- Error #NUM!: This error occurs if:
+ Unable to determine consistent step size in the date/time of the provided timeline.
+ The [seasonality] value provided is not within the valid range of 0 - 8784.
+ The [data completion] value provided is not equal to 0 or 1.
+ The [aggregation] value provided is not within the valid range of 1 - 7.
- Error #VALUE!: This error occurs if one or more of the [seasonality], [data completion], or [aggregation] parameters are not of numeric type.

Above is some information and examples of the FORECAST.ETS function in Excel, used for calculating or predicting future values. In the following articles, Mytour will further introduce you to the FORECAST.ETS.CONFINT function in Excel.