A rolling three-month average takes each month's spending together with the two months before it and averages them. In Excel, with monthly totals in column B starting at row 2, type =AVERAGE(B2:B4) in row 4 and copy it down. The first two months have no full window, so leave them empty or show #N/A so a chart skips them.
The basic formula
Put months in column A and monthly spending in column B. The average sits in column C, three rows into the list. In C4, write:
=AVERAGE(B2:B4)
Copy it down. Because the reference is relative, C5 becomes =AVERAGE(B3:B5), C6 becomes =AVERAGE(B4:B6), and the three-row window slides down with it. For C2 and C3, type =NA() so a chart leaves them out.
A worked example
Say six months of total spending are these made-up figures.
| Month | Spending (₹) | Rolling 3-month average (₹) | Working |
|---|---|---|---|
| Apr | 62,000 | #N/A | Not enough months yet |
| May | 71,000 | #N/A | Not enough months yet |
| Jun | 66,000 | 66,333 | (62,000 + 71,000 + 66,000) / 3 |
| Jul | 74,000 | 70,333 | (71,000 + 66,000 + 74,000) / 3 |
| Aug | 69,000 | 69,667 | (66,000 + 74,000 + 69,000) / 3 |
| Sep | 80,000 | 74,333 | (74,000 + 69,000 + 80,000) / 3 |
September's 80,000 looks like a jump, but the rolling average only moves from 69,667 to 74,333. That is what the average is for: it smooths a festival month so you can see the underlying direction.
How do I handle the first two months?
You have three choices. Leave them blank, which is honest. Use #N/A, which charts skip. Or use a shorter window with a note, such as =AVERAGE(B$2:B2) in row 2, which averages what exists so far. If you do the last, label the column so nobody reads the early values as full three-month averages.
One formula that fills down
To avoid typing the first two rows differently, use the same formula in every row of column C, starting at C2:
=IF(COUNT(B$2:B2)<3, NA(), AVERAGE(INDEX(B:B, ROW()-2):B2))
COUNT checks that three numbers exist so far. INDEX picks the cell two rows above, and the colon joins it to the current row's B cell, making a three-row range.
What is my average for the last three months?
For a single cell that always uses the latest three months, however many you have entered, use:
=AVERAGE(INDEX(B2:B100, COUNT(B2:B100)-2):INDEX(B2:B100, COUNT(B2:B100)))
It counts the filled months, then averages the last three. It assumes there are no gaps in the list and at least three months of data.
Chart the actual against the average
- Select the Month, Spending and Rolling average columns.
- Choose Insert, then Line with Markers.
- Make the spending line thin and pale, and the average line thick and dark.
- Alternatively, chart spending alone, right-click the line, choose Add Trendline, then Moving Average with Period 3.
The built-in trendline plots the same idea without helper cells, but it gives you no numbers to read, so keep column C if you want to quote the values.
To change the window, widen the range. A six-month average uses =AVERAGE(B2:B7) in row 7, and a twelve-month average smooths out a whole year of festivals, though it needs more than a year of data before the first value appears. A longer window reacts more slowly and a shorter one is noisier.
What the average can and cannot tell you
A rolling average lags. If spending drops this month, the average falls only by a third of the drop. It is good for spotting a drift upward over a quarter, not for catching a single bad week. It also treats a one-off, such as an annual premium, as a regular cost for three months, so remove or flag such items if you want to see everyday spending.
Months of totals come from a pivot or a SUMIFS. If you would rather start from imported statements, SpendixAI has reports and a dashboard built from the transactions you upload, and you can still download the numbers as CSV for a sheet like this.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


