Money tips

Flag unusually large payments by comparing them with your median in pounds

Find the largest transactions this month in the UK by flagging anything well above your median payment, with a £ worked example and conditional formatting.

Editorial illustration for Flag unusually large payments by comparing them with your median in pounds

The simplest way to spot an unusual payment in a long list is to compare each one with your typical payment. Work out the median amount for the month, multiply it by three, and flag everything above that line. It finds the odd ones for you, whatever size your month happens to be.

Why not just choose a fixed amount?

A fixed threshold, say £100, is easy to remember, but it suits one household and not another. If your usual payments are £5 and £10, a £60 payment is a surprise. If you pay rent, school costs and a car loan, £100 is routine. Tying the threshold to your own median makes it fit your life.

A worked example

Say ten payments in a fortnight are: £3.20, £12.50, £4.80, £38.00, £7.90, £120.00, £9.60, £27.45, £6.30, £215.00.

Sort them from smallest to largest: £3.20, £4.80, £6.30, £7.90, £9.60, £12.50, £27.45, £38.00, £120.00, £215.00. With ten values, the median is the average of the fifth and sixth, which is (£9.60 + £12.50) / 2 = £11.05. Three times the median is £33.15.

PaymentAbove £33.15?Action
£38.00YesLook it up
£120.00YesLook it up
£215.00YesLook it up
The other sevenNoSkim only

Three payments out of ten are flagged. The multiplier of three is a rule of thumb, not a standard; if too many items are flagged, try four, and if too few, try two.

How do I build the flag in a sheet?

With amounts in C2:C200, place this in D2 and fill down:

=IF(C2>3*MEDIAN($C$2:$C$200),"Check","")

The dollar signs lock the range so each row compares against the same median. For automatic highlighting, use conditional formatting with a custom formula:

=$C2>3*MEDIAN($C$2:$C$200)

Apply it to the range A2:C200 and choose a fill colour. This works in both Excel and Google Sheets. Keep blank rows out of the range or use a range that matches your data, and note that MEDIAN ignores empty cells but counts zeros.

What do I do with a flagged payment?

  • Find the original: an order email, a booking confirmation or a receipt.
  • Check the date. Does it match when you bought it?
  • Check the amount against the confirmation, down to the pence.
  • Check for a second, identical payment shortly afterwards.
  • If you cannot place it, ask your bank or card provider through the number or message route on your statement or card, rather than through a link in an unexpected message.

Does the multiplier hide anything?

Yes, one thing: many small payments. A flag on size never catches thirty £4.99 payments that are really one forgotten subscription charging weekly. Run a second look each month: group by merchant name and count. Anything appearing more than a few times with the same amount deserves a question.

Where an app can do some of this

SpendixAI is built around India and INR, so it is not aimed at UK readers, but it does include a Possible duplicate charges report for the same amount to the same merchant within two days, and a Report builder to filter and group. The median-and-threshold method above can be done by anyone in a plain sheet.

A short end-of-month routine

  1. Paste or type all payments for the month in one sheet.
  2. Mark recurring ones.
  3. Apply the median rule to everything else.
  4. Work through each flagged row with the list above.
  5. Write one line about anything you could not explain, and revisit it in a week.
SPENDIXAI JOURNAL

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

Explore your reports