GROUP_MOVING_AVERAGE
- Juliane Wetzel
Syntax
GROUP_MOVING_AVERAGE(<number>;[<number>])
Values: time series values
K: number of past values to compute the moving average from
Description
This function smooths a time series by applying a simple moving average of the past k values. Smoothing a time series helps remove random noise and leave the user with a general trend. You might use GROUP_HOLT_WINTERS for exponential smoothing.
This is an aggregate function.
Example
In the following example you have average stock price values per month over a three year period. Try to smooth the data to find the general trend.
Next, duplicate this source into a calculation sheet in order to work on it.
Click an empty column to bring up the Formula Builder and select GROUP_MOVING_AVERAGE
Use the Avg_Price as the Data and set the k at 5. This uses each previous 5 months to create a smoothed average for each data record.
The first record in the column MovingAverage_Price_5 is an average of the first 5 records of Avg_Price.
As the Moving_Average_Price_5 records are the average of the previous 5 Avg_Price records, the Moving_Average needs to match to the median of the Date and Ave_Price.
Create a new sheet and click the first open column to bring up the Formula Builder. Select GROUP_PREVIOUS to shift the Date column down two rows.
Use the same GROUP_PREVIOUS function to shift the Avg_Price down by two rows.
Finally, COPY the MovingAverage_Price_5 into the sheet.
The moving average values are now properly aligned with the median date and price the moving average is based on.
After having completed the workbook, a line chart would be a good tool to visualize your new smoothed data compared to your actual data.
