Budgeting

How many months of data do you need before you trust an average?

How many months of expense data you need before an average can be trusted, tested on 2, 3, 6 and 12 months with a festival month, plus a rule for categories.

Editorial illustration for How many months of data do you need before you trust an average?

Two months is too few to trust an average, three is a rough guide, six is usually good for steady categories and twelve is needed for anything lumpy, like insurance, travel or festivals. The test is simple: add the next month and see whether the average moves much. If it barely moves, you can use it. If it jumps, you do not have enough data yet.

How does the average move as you add months?

Take twelve months of hypothetical variable spending, in thousands of rupees, listed from December to November: 47, 41, 40, 43, 40, 41, 39, 42, 40, 44, 58, 43. October includes a festival month at ₹58,000. The total is ₹5,18,000.

Months used (most recent)Months includedAverageGap from 12-month average
2Oct, Nov₹50,500₹7,333 higher
3Sep, Oct, Nov₹48,333₹5,167 higher
6Jun to Nov₹44,333₹1,167 higher
12Dec to Nov₹43,167Baseline

The workings: (58 + 43) ÷ 2 = 50.5; (44 + 58 + 43) ÷ 3 = 48.33; (39 + 42 + 40 + 44 + 58 + 43) ÷ 6 = 44.33; and 518 ÷ 12 = 43.17, all in thousands. A short window that includes the festival month overstates the normal month. A window that misses it understates the year.

What does the median show that the average hides?

The median is the middle value when you sort the months. Sorted, our twelve values are 39, 40, 40, 40, 41, 41, 42, 43, 43, 44, 47 and 58. The middle two are 41 and 42, so the median is ₹41,500. The average is ₹43,167. The festival month pulls the average up but barely moves the median, so the median is a better picture of a typical month, and the average is a better picture of the year.

Use the median for a normal month, the average for a yearly total, and look at both.

How do you work this out in a sheet?

With the monthly values in B2:B13, December in B2 and November in B13, these work in Excel and Google Sheets.

  • Twelve-month average: =AVERAGE($B$2:$B$13)
  • Median: =MEDIAN($B$2:$B$13)
  • Last three months: =AVERAGE($B$11:$B$13)
  • Average without the highest month: =(SUM($B$2:$B$13)-MAX($B$2:$B$13))/(COUNT($B$2:$B$13)-1)

The last one gives (518 − 58) ÷ 11 = about 41.8, which is about 3% below the twelve-month average of 43.17. That small shift tells you the category is steady apart from the one festival month.

A rule for when a category average is trustworthy

  1. At least six months of data for steady categories such as groceries, fuel or commute.
  2. A full twelve months for lumpy ones such as insurance, school fees, travel and festival spending.
  3. Drop the highest month and recompute. If the answer moves by only a few percent, the average is stable.
  4. Look at the spread: if your lowest and highest months are far apart, show a range, not one number.

What if you only have a few months?

You can still use them. Say clearly that the figure is based on a short run, take the median as well as the average and use the higher of the two for a budget to stay on the cautious side. As months pass, update the figure, and note the date you last did.

If you import statements, SpendixAI has an overview and ready-made reports with CSV download, so you can export each month and watch how the figure behaves as more months arrive.

To remember

  • Two months mislead; six months guide; twelve months settle it.
  • Check the stability by removing the highest month.
  • Keep festival and annual items in their own lines.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports