Statistical
Moving average formula: smooth noisy weekly data with a trailing N-period window
Calculate a dynamic trailing N-period moving average with TAKE and AVERAGE, and emit NA() on incomplete warm-up rows.
Smooth week-to-week spikes without lag bugs
A 3-week trailing moving average averages the latest 3 observations so one-day promotions or weather dips don't distort trend tracking.
To compute the latest N-period moving average from a growing column without rewriting cell addresses, slice the bottom N cells with TAKE(B2:B6, -D2) and wrap in AVERAGE.
| Week (A) | Orders (B) | 3-Wk Moving Avg | WindowN (D2) |
|---|---|---|---|
| W1 | 110 | #N/A (warm-up) | 3 |
| W2 | 130 | #N/A (warm-up) | |
| W3 | 120 | 120.00 | |
| W4 | 150 | 133.33 | |
| W5 | 160 | 143.33 |
Latest N-period moving average with TAKE
With WindowN = 3 in D2, TAKE(B2:B6, -D2) grabs W3..W5 (120, 150, 160) whose average is 430 / 3 = 143.33.
=ROUND(AVERAGE(TAKE(B2:B6, -D2)), 2)Mark incomplete warm-up rows with NA()
Averaging partial 1-week or 2-week windows at the top of a column creates misleading early values; checking ROWS($B$2:B3) < $D$2 outputs #N/A until 3 full periods exist.
This marks missing data in cells. Arte Sheets charts currently convert #N/A to zero, so the marker does not yet create the Excel-style chart gap. Use a positive integer window no larger than the available numeric observations; a trailing average smooths variation but also lags changes.
=IF(ROWS($B$2:B3) < $D$2, NA(), AVERAGE(B2:B3))Try the example
Use the original values or change them to test your understanding.