How to Calculate Moving Averages in Google Sheets Using AVERAGE and OFFSET

Tracking revenue figures, server response times, website traffic metrics, or commodity prices often involves volatile daily fluctuations that obscure underlying long-term trends. A Simple Moving Average (SMA) smooths out short-term noise by calculating the mean of a fixed number of preceding data points across a sliding window. While static ranges like =AVERAGE(B2:B8) work for simple spreadsheets, hardcoding references makes formula updates cumbersome when testing different window sizes (such as 7-day, 14-day, or 30-day periods). In Google Sheets, combining the AVERAGE function with OFFSET allows financial and data analysts to construct dynamic, parameter-driven moving average formulas that adjust automatically.

Understanding How AVERAGE and OFFSET Build Rolling Windows

The standard AVERAGE function computes the numerical mean across a supplied range. The OFFSET function returns a dynamic range reference shifted by a specified number of rows and columns from a starting anchor cell, allowing you to define custom height and width dimensions:

=OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

By nesting OFFSET inside AVERAGE, you can instruct Google Sheets to look backwards from the current row by \(N\) rows. Unlike calculating weighted distributions with SUMPRODUCT for weighted moving averages or evaluating periodic yield with XIRR, the AVERAGE and OFFSET combination provides an agile, lightweight method to model rolling averages dynamically.

Structuring Your Data Table

To demonstrate a 7-day moving average, arrange your dataset in two columns:

• Column A: Chronological dates (e.g. A2:A31).
• Column B: Daily metric values, such as daily sales or active users (e.g. B2:B31).
• Cell E1: A dedicated parameter input cell storing the window duration (e.g. 7).

Organizing dates chronologically ensures the sliding window captures accurate historical sequence. If dates span irregular calendar periods, standardize them using functions like EOMONTH before applying rolling models.

Writing the Dynamic Moving Average Formula

Because a 7-day moving average requires at least 7 preceding data points to be statistically sound, formula evaluation should begin on row 8 (or include an error-handling conditional check). In cell C2, enter the following formula:

=IF(ROW() - ROW($B$2) + 1 < $E$1, "", AVERAGE(OFFSET(B2, 0, 0, -$E$1, 1)))

Here is how the formula works step-by-step:

1. ROW() - ROW($B$2) + 1: Calculates how many data rows exist from the start of the table down to the current row.
2. < $E$1: Compares the row count against the parameter in E1 (e.g. 7). If fewer than 7 periods are available, it returns an empty string (""), preventing premature averages based on incomplete windows.
3. OFFSET(B2, 0, 0, -$E$1, 1): Anchors at the current cell B2, with zero row and column shift. The negative height (-$E$1) instructs OFFSET to extend upward by 7 rows, capturing the current day and the previous 6 days.
4. AVERAGE(...): Computes the arithmetic mean across the 7 cells captured by OFFSET.

Dragging the Formula Down the Dataset

Select cell C2, click and hold the small blue fill handle in the lower-right corner of the cell, and drag it downwards across the remainder of your data column (e.g. down to C31).

Rows 2 through 7 remain blank. Starting at row 8, cell C8 calculates the exact average of rows 2 through 8. Cell C9 calculates rows 3 through 9, and so on. If you subsequently decide to evaluate a 14-day or 30-day moving average, simply change the number in E1 from 7 to 14 or 30; every calculation across the sheet updates instantaneously.

Plotting the Moving Average on a Trend Chart

To visualize the smoothed trend against volatile raw data:

1. Highlight columns A, B, and C (from row 1 down to your last data entry).
2. Click Insert > Chart.
3. In the Chart editor sidebar, select Line chart as the chart type.
4. Notice how the raw daily values (Column B) display sharp spikes and dips, while the moving average line (Column C) renders a smooth, legible trajectory reflecting real performance momentum.

Pairing rolling calculations with text reporting formulas like TEXTJOIN with FILTER allows analysts to automatically generate executive summaries that highlight upward or downward trend direction without manual recalculation.