The `FORECAST.ETS` function in Excel is used to predict a future value based on existing historical data using an exponential smoothing algorithm. This function is useful for analyzing time series data and making predictions about trends.
Here’s how to use the `FORECAST.ETS` function:
Syntax
FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])
Arguments
- target_date (required):
- The date for which you want to forecast a value. This can be a specific date, a numeric representation of a date, or a reference to a cell containing the date.
- values (required):
- The historical values or data points that you want to use to generate the forecast. This should be a range or an array of numeric values.
- timeline (required):
- The timeline or dates corresponding to your historical data. This should be a range or an array with numeric values representing dates in the same intervals as your historical values.
- seasonality (optional):
- A numeric value indicating the seasonal pattern to use. You can set it to zero for no seasonality, or use 1 for automatic detection.
- data_completion (optional):
- This determines how the function handles missing data points. Use 0 to ignore missing points or 1 (or TRUE) to handle them by interpolation.
- aggregation (optional):
- Determines how to aggregate multiple values that have the same time point. Options include:
- 1 (or AVERAGE) for averaging,
- 2 (or COUNT) for counting,
- 3 (or COUNTA) for counting non-empty,
- 4 (or MAX) for finding the maximum,
- 5 (or MEDIAN) for finding the median,
- 6 (or MIN) for finding the minimum, and
- 7 (or SUM) for summing.
Example
Suppose you have historical sales data from January to December in cells `B2:B13` and the corresponding months in cells `A2:A13`. You want to forecast sales for the next month (January of the next year).
- Target Date: A14 (e.g., 01-01-2024 if stored as a text date or equivalent date format)
- Values: B2:B13
- Timeline: A2:A13
If you want automatic seasonality detection and default handling for the rest, the formula will look like this:
=FORECAST.ETS(A14, B2:B13, A2:A13, 1)
Tips
- Make sure your timeline data is equally spaced for accurate forecasting.
- Ensure that the `timeline` and `values` ranges are of the same size.
- The `target_date` must be within the timeline range or at a future point based on it.
- Seasonality detection will automatically try to identify patterns if set to 1.
Using the `FORECAST.ETS` function can be incredibly powerful for analyzing trends and seasonal patterns in your data, helping you make informed business decisions.
Unlock Your Potential
Excel
Access
Power BI
