Confident Cells

Trip payments and balances: practice exercises

Paid so far, balances, payment status, subsidies, refunds. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.

Your turn

In F2, work out how much each pupil has paid so far across the four instalment months. A blank means nothing was paid that month.

ABCDEF
1PupilSeptemberOctoberNovemberDecemberPaid so far
2Esme Smith£20.00£30.00
3Ibrahim Johnson£5.00£30.00£12.50£10.00
4Arjun Clarke£40.00£12.50£40.00
5Poppy Wright£5.00£30.00£25.00£5.00
6Isla Brown£7.50£10.00£25.00£25.00
7Harry Okafor£40.00£40.00
8Maya Wood£7.50£30.00
9Evie Hussain£5.00£7.50£12.50
10Ellie Walker£5.00
11Freya Smith£40.00£12.50£15.00£25.00
12Theo Evans£20.00£15.00£12.50£20.00
13Sofia Smith£20.00£15.00£5.00£40.00
14Hana Begum£40.00£25.00£40.00
15Aryan Begum£15.00£5.00£40.00
16Oscar Clarke£30.00£12.50
17Kai Wright£40.00£15.00£40.00£20.00
18Jack Turner£7.50£20.00
19Aisha Hall£15.00£15.00£5.00£25.00
20Leah Ahmed£20.00£25.00£40.00£30.00
21Kacper Nowak£10.00£12.50

How it works

A trip sheet tracks instalments. Add them up for the amount paid so far, and subtract that from the cost (kept in one locked cell) for the balance.

IFS tests conditions in order and stops at the first one that's true, so put "nothing paid" first, then "less than the cost", and TRUE last to catch everyone else.

=SUM(B5:D5) =$I$1-E2 =IFS(E2=0,"Not paid",E2<$I$1,"Part paid",TRUE,"Paid") =MAX(0,E2-$I$2)

Paid so far, and what's owed

Add up the instalments for the amount paid, with blanks counting as nothing. Keep the cost in one cell and lock it with $, so the balance is =$I$1-paid on every row.

Worked example

ABCDEFGHI
1PupilSepOctNovPaidOwesStatusCost£150.00
2Ana£50.00£50.00£50.00
3Bilal£50.00
4Chloe
5Dan£100.00£25.00
  1. =SUM(B2:D2) in E2. Paid so far: Ana £150, Bilal £50, Chloe nothing, Dan £125.
  2. =$I$1-SUM(B2:D2) in F2. The cost in I1 minus what they've paid. Dan still owes £25.
  3. =IFS(SUM(B2:D2)=0,"Not paid",SUM(B2:D2)<$I$1,"Part paid",TRUE,"Paid") in G2. IFS stops at the first true test. Nothing paid comes first, then less than the cost, and TRUE catches everyone else.

Subsidies and refunds

  • SUMIF(eligible,"Yes",owes) adds up what the subsidised pupils still owe.
  • If the cost drops, the refund is anything paid above the new cost, but never a minus figure: =MAX(0, paid − new cost).

What you'll practise

  1. In F2, work out how much each pupil has paid so far across the four instalment months. A blank means nothing was paid that month.
  2. The trip costs the amount in H1. In C2, work out how much each pupil still owes.
  3. In C2, show Not paid if a pupil has paid nothing, Part paid if they've paid something but less than the cost in H1, and Paid otherwise.
  4. Pupils marked Yes in column D are eligible for a subsidy. In H1, work out the total they still owe.
  5. The trip now costs the lower amount in H2. In C2, work out the refund due to each pupil: anything they've paid above the new cost, or 0 if they haven't paid that much.