Excel intermediate moving-average excel-formulas data-analysis time-series

How to Calculate a Moving Average in Excel?

Calculate Moving Averages is a Excel function that calculates the average of a specified number of recent entries in a time series.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

Moving averages help smooth out data fluctuations. This guide shows you how to calculate a rolling average using Excel formulas.

Generate This Formula Browse Library

The Formula

Prompt

"Calculate a rolling 7-day or N-period moving average from a time series column using AVERAGE with OFFSET"

Excel 
=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

  1. OFFSET function defines the starting point for the range based on the current row.
  2. ROW() function returns the current row number to adjust the range dynamically.
  3. The '0' in OFFSET indicates no horizontal shift, keeping it in the same column.
  4. The '7' specifies the height of the range, allowing for a 7-day average.
  5. 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

Prompt

"Values: 10, 20, 30, 40, 50, 60, 70 in A2:A8"

Excel 
Average for A8: 50
Prompt

"Values: 5, 15, 25 in A2:A4"

Excel 
Average for A4: 15

Frequently 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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples