One big purchase can make a normal month look like a spending spree and push your average up for good. Two simple fixes show your real pattern: use the median of your months instead of the average, and flag the one-off so you can total the month with and without it. Both take a few formulas and a six-month list.
How much can one purchase distort the average?
Say your monthly spending is close to the same every month, apart from April, when you bought a laptop for $1,400. Here are six months of made-up totals.
| Month | Total spent |
|---|---|
| January | $2,310 |
| February | $2,275 |
| March | $2,340 |
| April | $3,845 |
| May | $2,290 |
| June | $2,330 |
The six months add up to $15,390, so the average is $2,565. That is higher than five of the six months. If you used $2,565 to plan July you would be planning for a month you have never actually had.
What does the median say instead?
The median is the middle value when the months are sorted. Order them: 2,275, 2,290, 2,310, 2,330, 2,340, 3,845. With six months, the median is halfway between the third and fourth, which is 2,310 plus 2,330 divided by two, or $2,320. One extreme month barely moves it. In a sheet with the months in B2:B7 use =AVERAGE($B$2:$B$7) and =MEDIAN($B$2:$B$7) side by side and watch the gap. When they are far apart, a one-off is at work.
How do I remove the one-off properly?
Do not delete the laptop. It was real money. Instead mark it so you can total both ways. In your transaction list, add a column D called One-off and type Y on the laptop row. Then:
- Month total with everything: =SUMIFS($C$2:$C$500,$E$2:$E$500,"2025-04"), where column E holds the month text.
- Month total without one-offs: =SUMIFS($C$2:$C$500,$E$2:$E$500,"2025-04",$D$2:$D$500,"<>Y").
- All the one-offs in a year: =SUMIFS($C$2:$C$500,$D$2:$D$500,"Y").
Take $1,400 out of April and it becomes $2,445. The six-month average becomes 2,310 plus 2,275 plus 2,340 plus 2,445 plus 2,290 plus 2,330, which is $13,990, divided by 6, giving $2,331.67. That is far closer to what a normal month feels like, and close to the median of $2,320.
What counts as a one-off?
Be strict, or the label stops meaning anything. A reasonable test: it is large compared with your usual transactions, you did not make a similar purchase in the last few months, and you do not expect to repeat it next month. A laptop, a deposit on a flat, a medical bill and a wedding gift usually pass. Groceries, rent and a phone bill do not, however big they are. Things that come around yearly, such as an insurance renewal, are better handled by spreading them over twelve months than by calling them one-offs.
Which number do I plan with?
Plan the month with the without-one-offs total, then keep a separate pot for the big, irregular things. If the flagged rows add up to $4,800 over a year, setting aside $400 a month for them keeps the ordinary months honest. That is a planning choice, not a rule, and it is yours to adjust.
What if I buy something big every few months?
Then it is not really a one-off, and treating it as one will leave your plan short. If the flagged rows over a year come to something like a laptop, a flight and a dental bill, that is a pattern of lumpy spending. Add the yearly total of flagged rows, divide by twelve, and include that amount in your monthly plan as a separate line. The monthly figure stays honest, and the lumps stop feeling like surprises.
A short routine
- At each month end, scan the three largest rows.
- Mark real one-offs with Y.
- Record both totals, with and without.
- Look at the median of the last six months before you set next month's plan.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

