How to Calculate a Moving Average in Excel?
Moving averages help smooth out data fluctuations. This guide shows you how to calculate a rolling average using Excel formulas.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Calculate a rolling 7-day or N-period moving average from a time series column using AVERAGE with OFFSET"
=AVERAGE(OFFSET(A2, ROW()-ROW(A$2), 0, 7, 1))This formula calculates the average of a specified number of recent entries in a time series.
Step-by-step breakdown
- OFFSET function defines the starting point for the range based on the current row.
- ROW() function returns the current row number to adjust the range dynamically.
- The '0' in OFFSET indicates no horizontal shift, keeping it in the same column.
- The '7' specifies the height of the range, allowing for a 7-day average.
- AVERAGE function computes the average of the values in the defined OFFSET range.
Edge cases & warnings
- If there are fewer than 7 entries, the formula will return an average of available entries.
- Blank cells within the range may lead to inaccurate averages.
- If the formula is dragged down too far, it may reference empty cells beyond the data range.
- Using a non-numeric value in the range will result in an error.
Examples
"Values: 10, 20, 30, 40, 50, 60, 70 in A2:A8"
Average for A8: 50"Values: 5, 15, 25 in A2:A4"
Average for A4: 15Frequently asked questions
What is a moving average?
A moving average smooths out data by averaging values over a specified number of periods.
Can I change the period from 7 to another number?
Yes, simply replace '7' in the formula with your desired period.
What happens if my data has gaps?
Gaps will affect the average; ensure your data is complete for accurate results.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.