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.
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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Pupil | September | October | November | December | Paid so far |
| 2 | Esme Smith | £20.00 | £30.00 | |||
| 3 | Ibrahim Johnson | £5.00 | £30.00 | £12.50 | £10.00 | |
| 4 | Arjun Clarke | £40.00 | £12.50 | £40.00 | ||
| 5 | Poppy Wright | £5.00 | £30.00 | £25.00 | £5.00 | |
| 6 | Isla Brown | £7.50 | £10.00 | £25.00 | £25.00 | |
| 7 | Harry Okafor | £40.00 | £40.00 | |||
| 8 | Maya Wood | £7.50 | £30.00 | |||
| 9 | Evie Hussain | £5.00 | £7.50 | £12.50 | ||
| 10 | Ellie Walker | £5.00 | ||||
| 11 | Freya Smith | £40.00 | £12.50 | £15.00 | £25.00 | |
| 12 | Theo Evans | £20.00 | £15.00 | £12.50 | £20.00 | |
| 13 | Sofia Smith | £20.00 | £15.00 | £5.00 | £40.00 | |
| 14 | Hana Begum | £40.00 | £25.00 | £40.00 | ||
| 15 | Aryan Begum | £15.00 | £5.00 | £40.00 | ||
| 16 | Oscar Clarke | £30.00 | £12.50 | |||
| 17 | Kai Wright | £40.00 | £15.00 | £40.00 | £20.00 | |
| 18 | Jack Turner | £7.50 | £20.00 | |||
| 19 | Aisha Hall | £15.00 | £15.00 | £5.00 | £25.00 | |
| 20 | Leah Ahmed | £20.00 | £25.00 | £40.00 | £30.00 | |
| 21 | Kacper 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
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Pupil | Sep | Oct | Nov | Paid | Owes | Status | Cost | £150.00 |
| 2 | Ana | £50.00 | £50.00 | £50.00 | |||||
| 3 | Bilal | £50.00 | |||||||
| 4 | Chloe | ||||||||
| 5 | Dan | £100.00 | £25.00 |
=SUM(B2:D2)in E2. Paid so far: Ana £150, Bilal £50, Chloe nothing, Dan £125.=$I$1-SUM(B2:D2)in F2. The cost in I1 minus what they've paid. Dan still owes £25.=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
- 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.
- The trip costs the amount in H1. In C2, work out how much each pupil still owes.
- 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.
- Pupils marked Yes in column D are eligible for a subsidy. In H1, work out the total they still owe.
- 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.