Getting started

Number of payments, average and biggest spend: read your month in three figures

Find your monthly transaction count, average payment and largest spends from bank statements in A$, with formulas that work in Excel and Google Sheets.

Editorial illustration for Number of payments, average and biggest spend: read your month in three figures

Three figures describe a month of spending better than the total alone: how many payments you made, the average payment, and the biggest few. All three come from the same list of statement rows, and a few formulas produce them in a minute. Here is how to get them, and how to read them without fooling yourself.

Step one: build one clean list

Export last month from each bank account and card as a spreadsheet or CSV file. Put everything in one sheet, with the date in column A, a description in B and the amount in C, using positive numbers for money out. Delete credits, salary and transfers between your own accounts, and leave out card repayments so purchases are not counted twice. What remains should be spending only.

How do I get the three figures?

  • Count: =COUNT($C$2:$C$300) counts numeric rows, which is the number of payments.
  • Total: =SUM($C$2:$C$300).
  • Average: =AVERAGE($C$2:$C$300), or total divided by count.
  • Largest single payment: =MAX($C$2:$C$300).
  • Top five together: =SUMPRODUCT(LARGE($C$2:$C$300,{1,2,3,4,5})), which works in both Excel and Google Sheets.

Say a month gives 58 payments totalling A$2,940.00. The average is A$50.69, because 58 times 50 is 2,900 and the remaining 40 divided by 58 is about 0.69. If the single largest payment is A$1,150.00, then that one row is about 39 percent of the month, since 1,150 divided by 2,940 is 0.39.

What happens to the average when one payment is huge?

It moves a lot. Remove the A$1,150.00 and the other 57 payments total A$1,790.00, so their average is about A$31.40. The first average of A$50.69 describes nobody's real day; the second is closer to the daily pattern. Whenever the largest payment is a big share of the total, read the average with that payment taken out.

How do I track this over months?

Keep a small log with one row per month. After three months it may look like this.

MonthPaymentsTotalAverage
August61A$2,688.00A$44.07
September58A$2,940.00A$50.69
October66A$3,102.00A$47.00

October has the most payments and the highest total, yet the average sits between the other two. That means the extra spending came from extra payments, not from larger ones, a useful hint when you decide what to look at first.

When is a single row worth a second look?

Sort the list by amount, largest first, and read the top ten descriptions aloud. Some will be expected: rent, a bill, an insurance renewal. Others you will not remember, and those are the ones to open. A repeat charge you did not notice shows up here more often than anywhere else, because it is large enough to sit near the top yet small enough to slip past a glance at the balance.

How does the count compare with the average?

Read them as a pair. Total divided by count gives the average, so if the count rises and the average falls, the extra payments were small ones. If the count stays level and the average rises, you made bigger payments, or one very large one. Both stories can end in the same total, and the fix for each is different: fewer small purchases in the first case, a closer look at specific large items in the second.

Checklist for next month

  1. Download all accounts on the same day each month.
  2. Remove transfers and card repayments.
  3. Run count, total, average, largest and top five.
  4. Add the line to your log.
  5. Open any large row you cannot name.
SPENDIXAI JOURNAL

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

Explore your reports