The windowSize parameter
What it does
Moving average window: "w", "w:min" or "w:min:kind". Empty: the window size from Settings (3 by default).
- w — window size in rows (a whole number of at least 1, not larger than the number of rows).
- min — how many values the window needs to give an average. Empty: all w values; a missing value in the window then gives ".". With "3:2" a window of 3 rows is averaged over its values as soon as at least 2 are there.
- kind — 0 or empty: trailing window, the average is on the last row of the window (the current row and the w − 1 rows before it); 1: prior window, the w rows before the current row (SPSS PMA); 2: centered window around the current row (SPSS MA; an even window averages two neighbouring spans, as SPSS does).
Rows where the window is not complete show ".": the first w − 1 rows for the trailing window, the first w rows for the prior window, both ends for the centered window.
Settings keep only the window size; a minimum or a kind is written in the formula.
Values
Required; default 3.
Should be a whole number of at least 1
In plain words
A shop owner had sales of six days: 40, 80, 60, 100, 80 and 120. With windowSize 3, day 5 showed the average of days 3, 4 and 5, which is 80. With windowSize 5, day 5 showed the average of all five days, which is 72, and the line was smoother. But with 3 the first two days show a dot, and with 5 the first four days do. Now she can pick a small window to follow the days closely, or a big one to see only the slow trend. Writing 3:2 means a window of 3 days is enough when 2 of them have a number.
Where you use it
STATS_MA— Creates new columns with the moving average of each selected column.
Examples
3— trailing window of 3 rows"3:2"— window of 3 rows, averaged when at least 2 values are there"5::2"— centered window of 5 rows, all 5 values needed"3:3:1"— the 3 rows before the current row (SPSS PMA)
Get it in your sheet
ALS Stats is a free add-on for Google Sheets™.
Install ALS Stats from Google Workspace Marketplace (opens in a new tab)