GROUP_HOLT_WINTERS

Owned by Juliane Wetzel
Last updated: May 09, 2023

Syntax

GROUP_HOLT_WINTERS(<number>;<number>;<number>;[<number>])

values: series values
α: data smoothing factor  (between 0.0 and 1.0)
β: trend smoothing factor (between 0.0 and 1.0)
h: prediction range (how far the future should be predicted) - optional, default = 1

Description

This function computes Holt-Winters double exponential smoothing (non-seasonal) on a time series. Smoothing a time series helps remove random noise and leave the user with a general trend. Whereas in the simple moving average (GROUP_MOVING_AVERAGE) the past observations are weighted equally, exponential smoothing assigns exponentially decreasing weights over time. This gives a stronger weight to more recent values and can lead to better predictions.

This function can't be used referring to the same sheet, so make sure to use a different sheet than the value source sheet. 

How to set alpha (data smoothing) and beta (trend smoothing). Both can be set with values between 0.0 and 1.0)

Alpha - Set a larger data smoothing value to reduce a greater amount of noise from the data. Use caution as setting the data smoothing value too high when your data doesn't have much noise can reduce data quality.
Beta - Set a larger trend smoothing value to get better quality data over a longer series. Use caution as setting the trend value too high can degrade the quality of the data over shorter time periods

This is an aggregate function.

Example

In the following example we have price values per month over a two year period. Let's try to predict the first 5 months of the third year using double exponential smoothing. 

  1. Add five additional rows in the date column to better visualize the results for 5 months of future predictions.
  2. Create a new worksheet in your workbook by duplicating the source sheet. 
  3. Create a group key using GROUPBY(1).
  4. Sort the timeline using GROUP_SORT_ASC(#Monthly_Sales!Date).
  5. Click the  Fx  button on the formula line to display the formula builder and select GROUP_HOLT_WINTERS.
  6. Use the monthly sales column for the value's argument.

As this example uses a small data set, the alpha and beta arguments are set low at 0.2.

With a larger data set, you can set the alpha and beta arguments higher as there is more data to allocate into making the predictions. Allocating too many data resources with a smaller data set can make predictions less reliable as there is not enough steady data to understand trends.

The Formula bar looks like this for your predicted values:

GROUP_HOLT_WINTERS(#Monthly_Sales;0.2;0.2;5)

There are two missing values for the rows, GROUP_HOLT_WINTERS needs these two values to compute the first prediction. Also, these predictions are 5 points in the future and currently don't match up with the date row.

To correct this, modify your Formula bar to:

GROUP_PREVIOUS(IF(ISNULL(#Monthly_Sales);null;GROUP_HOLT_WINTERS(#Monthly_Sales;0.2;0.2;5));5)

This uses the functions GROUP_PREVIOUS to move the predictions 5 rows down to line up with the correct date. This also uses the function ISNULL to correct the error for the missing values in the rows with no values.

The sheet now displays the predicted data using the Holt-Winters exponential smoothing method.

Your finished workbook displays the full date range, the original values, and the predicted values.

After having completed the workbook, use a Line Chart to visualize your new smoothed data predictions compared to your actual data.