Moving Average Formulas in Excel
Moving Average Formula is a Excel function that for each row, offset grabs the current cell and 6 rows above it (7 total), and average calculates the mean. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Smooth out noisy data with moving averages. Essential for sales trends, stock analysis, and KPI dashboards.
The Formula
"Calculate a 7-day simple moving average of daily sales"
=IFERROR(AVERAGE(OFFSET(B2,-6,0,7,1)),"")For each row, OFFSET grabs the current cell and 6 rows above it (7 total), and AVERAGE calculates the mean. This creates a rolling 7-day average.
Step-by-Step Breakdown
- OFFSET(B2,-6,0,7,1) creates a reference 6 rows up from B2, 7 rows tall, 1 column wide
- AVERAGE calculates the mean of those 7 values
- As you drag down, the window slides with each row
- IFERROR handles the first 6 rows where a full 7-day window doesn't exist
- For non-volatile alternative: =AVERAGE(B2:INDIRECT("B"&ROW()-6))
Edge Cases & Warnings
- First N-1 rows can't have a full window — IFERROR returns blank
- OFFSET is volatile (recalculates every time) — slow on large datasets
- Missing data (blanks) are excluded from AVERAGE, reducing the effective window
- For trailing vs centered average, adjust the offset direction
Examples
"Daily sales: [100, 120, 90, 110, 130, 95, 105]"
7-day SMA = 107.14"Only 3 days of data"
Returns blank (not enough data for 7-day window)Frequently Asked Questions
SMA vs EMA — which should I use?
SMA (Simple Moving Average) gives equal weight to all periods. EMA (Exponential Moving Average) gives more weight to recent data. Use EMA for faster trend detection, SMA for smoother lines.
How do I change the period?
Replace -6 with -(period-1) and 7 with your desired period. For a 30-day average: =AVERAGE(OFFSET(B2,-29,0,30,1)).
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.