Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate a moving average in Excel and Google Sheets
Topic:AVERAGE & central tendencyExcel & Google Sheets
=AVERAGE(B2:B4)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Day | Value |
| 1 | 10 |
| 2 | 14 |
| 3 | 12 |
| 4 | 18 |
| 5 | 16 |
=AVERAGE(B2:B4)→12
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Decide on window size N (e.g. 3 periods).
- 2In the first output row where a full window exists (row N+1 if data starts at row 2), enter =AVERAGE(B2:B4) for a 3-period window.
- 3Copy the formula down — relative references shift automatically, giving =AVERAGE(B3:B5) in the next row, etc.
- 4Leave output cells blank for the first N-1 rows where a full window is not yet available.
- 5For a self-adjusting formula, use OFFSET: =AVERAGE(OFFSET(B4,0,0,-3,1)) copied down from the third data row.
Tips
- A 3-period MA in row 4 averages rows 2, 3, and 4. Row 5 averages rows 3, 4, 5 — copy the formula down to continue.
- For an exponential moving average (EMA), a formula approach is more complex — consider using a helper column.
- Excel's Data Analysis ToolPak includes a built-in Moving Average tool (Data > Data Analysis > Moving Average).
- In Google Sheets, there is no equivalent ToolPak; the formula approach is the standard method.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Should I use AVERAGE or AVERAGEIF for a moving average?
Plain AVERAGE with a relative range is the right tool. AVERAGEIF adds conditions, which are not needed for a simple rolling window.
How do I handle the first N-1 rows where the window is incomplete?
Either leave them blank, use =IF(ROW()-ROW($B$2)+1>=N, AVERAGE(OFFSET(...)), "") to suppress early values, or accept a partial window average for early rows.
More on AVERAGE & central tendency
See all →How-to
How to calculate an average
Use the AVERAGE function to calculate the arithmetic mean of a range of numbers in Excel or Google Sheets.
How-toHow to average ignoring zeros
Use AVERAGEIF with a "<>0" criterion to calculate an average that excludes zero values from the calculation.
How-toHow to average ignoring blank cells
AVERAGE naturally ignores blank cells; use AVERAGEIF with "<>" to also ignore text placeholders like dashes.
How-toHow to average with a condition
Use AVERAGEIF to compute the mean of values in one column where a corresponding column meets a specified criterion.
How-toHow to find the median of a range
Use the MEDIAN function to find the middle value in a dataset — the value that half the data falls above and half below.
How-toHow to find the most common value
Use MODE for numbers or COUNTIF-based logic for text to find the value that appears most frequently in a dataset.