Moving average calculation
IntermediateMonth-to-month traffic numbers can be noisy. A moving average smooths out those spikes and dips so you can see the underlying trend more clearly.
A moving average uses a sliding window. A 3-month moving average uses the current month plus the previous 2 months. A 6-month moving average uses the current month plus the previous 5 months.
In real reporting, teams often compare short-term and long-term windows side by side. This sheet includes both:
- 3M average uses the window size in C2
- 6M average uses the window size in D2
The challenge is writing one formula pattern that works for both columns and all months. This is where OFFSET shines.
How OFFSET works:
=OFFSET(reference, rows, cols, [height], [width])
OFFSET returns a reference relative to a starting cell, and that reference can be a single cell or a multi-cell range. You can use the height argument to control how many rows the returned range spans. Pass a negative number to expand the range upward from the reference cell rather than downward.
Your task:
The spreadsheet shows 12 months of website visitor data. In columns C and D, calculate rolling averages using the window sizes in row 2.
Enter the first 3M formula in C6 and copy down to C15. Then copy that pattern to the 6M column from D9 down to D15.
Need some help?
Hint 1
Use the window size row as a dynamic input. That way the same formula can work for both columns C and D.
Hint 2
Wrap OFFSET inside AVERAGE so the returned range size is controlled by the window value.
Hint 3
Lock the data column ($B6), but let the window-size column move when copied right (C$2 then D$2).
Moving average calculation
IntermediateMonth-to-month traffic numbers can be noisy. A moving average smooths out those spikes and dips so you can see the underlying trend more clearly.
A moving average uses a sliding window. A 3-month moving average uses the current month plus the previous 2 months. A 6-month moving average uses the current month plus the previous 5 months.
In real reporting, teams often compare short-term and long-term windows side by side. This sheet includes both:
- 3M average uses the window size in C2
- 6M average uses the window size in D2
The challenge is writing one formula pattern that works for both columns and all months. This is where OFFSET shines.
How OFFSET works:
=OFFSET(reference, rows, cols, [height], [width])
OFFSET returns a reference relative to a starting cell, and that reference can be a single cell or a multi-cell range. You can use the height argument to control how many rows the returned range spans. Pass a negative number to expand the range upward from the reference cell rather than downward.
Your task:
The spreadsheet shows 12 months of website visitor data. In columns C and D, calculate rolling averages using the window sizes in row 2.
Enter the first 3M formula in C6 and copy down to C15. Then copy that pattern to the 6M column from D9 down to D15.
Need some help?
Hint 1
Use the window size row as a dynamic input. That way the same formula can work for both columns C and D.
Hint 2
Wrap OFFSET inside AVERAGE so the returned range size is controlled by the window value.
Hint 3
Lock the data column ($B6), but let the window-size column move when copied right (C$2 then D$2).