Rolling Average Spending
Calculate a 3-month or 6-month rolling average for each budget category to smooth out irregular expenses.
=AVERAGE(OFFSET(current_cell, -months+1, 0, months, 1)) How It Works
A rolling average calculates the mean of the last N months of spending in a category. This smooths out one-time spikes and seasonal variation, giving a clearer picture of actual spending patterns.
Syntax
=AVERAGE(B2:B4)For a dynamic 3-month rolling average using OFFSET:
=AVERAGE(OFFSET(B6, -2, 0, 3, 1))Where B6 is the current month and -2 looks back two rows to include 3 months total.
Example
Groceries Spending:
| Month | Spent | 3-Month Avg | 6-Month Avg |
|---|---|---|---|
| Jan | $420 | - | - |
| Feb | $380 | - | - |
| Mar | $510 | $437 | - |
| Apr | $390 | $427 | - |
| May | $450 | $450 | - |
| Jun | $620 | $487 | $462 |
| Jul | $400 | $490 | $458 |
3-Month Formula (Mar): =AVERAGE(B2:B4) = $437
6-Month Formula (Jun): =AVERAGE(B2:B7) = $462
The June spike of $620 barely moves the 6-month average, making it easier to spot whether spending is truly rising or just fluctuating.
Setting Up a Rolling Average Tracker
| A | B | C | D |
|---|---|---|---|
| Month | Groceries | 3-Mo Avg | 6-Mo Avg |
| Jan | 420 | ||
| Feb | 380 | ||
| Mar | 510 | =AVERAGE(B2:B4) | |
| Apr | 390 | =AVERAGE(B3:B5) | |
| May | 450 | =AVERAGE(B4:B6) | |
| Jun | 620 | =AVERAGE(B5:B7) | =AVERAGE(B2:B7) |
Variations
Using OFFSET for Flexibility
Make the number of months a variable in a cell (e.g., F1 = 3):
=AVERAGE(OFFSET(B7, -F1+1, 0, F1, 1))Change F1 to 6 and the same formula gives a 6-month rolling average.
Weighted Rolling Average
Give more weight to recent months:
=(B6*3 + B5*2 + B4*1) / 6This weights the most recent month 3x and the oldest month 1x.
Rolling Average with AVERAGEIF
Exclude months where spending was zero (e.g., new category):
=AVERAGEIF(B2:B4, ">0")Pro Tips
-
3-month vs 6-month - shorter windows react faster to changes, longer windows smooth more noise
-
Watch the trend - if the rolling average keeps rising month over month, spending is genuinely increasing
-
Use for budgeting - a 3-month rolling average often works better than a single month for setting next month’s budget target
-
Combine with SPARKLINE -
=SPARKLINE(C2:C13)next to the rolling average column shows the trend visually -
Handle early months - use
=IF(ROW()-1>=3, AVERAGE(...), "")to avoid partial averages in the first few months
Common Errors
- Including empty cells: AVERAGE ignores empty cells but treats 0 as a value - be consistent
- Wrong OFFSET direction: The row offset should be negative to look backward in time
- Mixing time periods: Make sure all values in the range represent the same interval (all monthly, all weekly, etc.)