Ravensworth Court splits its quarterly service charge across eight flats by floor area. The bill was £12,850.00. The eight demands came to £12,875.70, and the leaseholder of Flat 5 added them up.
| Method | Demands total | Against the bill |
|---|---|---|
| Rounded lease percentages | £12,875.70 | £25.70 over |
| Each share rounded to the penny | £12,850.03 | 3p over |
| Running total rounded | £12,850.00 | Exact |
Scope.
ROUND,SUM,MAXandABSwork in every version of Excel.=MAX(ABS(...))over a range spills natively in Excel 365 and 2021. In older versions, confirm it with Ctrl+Shift+Enter.
01Rounded Percentages Billed £25.70 Too Much
The sheet turned each flat's area into a percentage first, rounded to one decimal place so the demand letter would print a tidy figure. Then it charged that percentage of the bill. In C2 and D2, filled to row 9:
C2: =ROUND(B2/$B$10,3)
D2: =ROUND($D$11*C2,2)
Ravensworth Court's Quarterly Split
Eight flats in A2:G9, the totals in row 10 and the bill, £12,850.00, in D11. C is the old percentage, rounded to 0.1%, and D the demand built on it. E is each flat's exact share of the bill by floor area, F that share rounded to the penny, and G the balanced demand from the running total.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Each percentage moved by up to 0.05 points, and the moves did not cancel. The eight percentages add up to 100.2%, so the demands asked for 100.2% of the bill: £25.70 more than the contractors were paid.
Flat 5 is 49 m² of 560, which is 8.75%. Rounded, it became 8.8%, and the demand was £1,130.80 against a fair £1,124.38.
Scenario: Enter the eight areas in B2:B9 and =SUM(B2:B9) in B10. Put =ROUND(B2/$B$10,3) in C2, fill it down, and check that =SUM(C2:C9) returns 1.002 rather than 1.
02Work From the Areas, Round Only the Money
The percentage is a step you do not need. Divide the bill by the total area and multiply by the flat's area, in one formula, and round only the result:
=ROUND($D$11*B2/$B$10,2)
Every flat now sits within half a penny of its exact share, which column E shows unrounded. If the letter must print a percentage, show =B2/$B$10 with a percentage format. Formatting changes what you see, not what the formula charges.
| Flat | Old demand | Rounded share | Difference |
|---|---|---|---|
| Flat 3 | £1,953.20 | £1,950.45 | £2.75 over |
| Flat 5 | £1,130.80 | £1,124.38 | £6.42 over |
| Flat 2 | £2,017.45 | £2,019.29 | £1.84 under |
Six flats were overcharged and two undercharged. The undercharged two are why nobody noticed from a single letter.
Scenario: Enter =ROUND($D$11*B2/$B$10,2) in F2 and fill it to F9. Check that F6 returns 1,124.38 and that =SUM(F2:F9) lands within pennies of the bill in D11.
03Rounding Each Share Still Left 3p Over
The fixed column totals £12,850.03. Rounding each row to the penny is still eight small errors, and nothing makes them cancel.
At Ravensworth Court they all went the same way. Flats 5, 6 and 7 come to exactly half a penny: £1,124.375, £2,088.125 and £1,124.375. ROUND takes a half away from zero, so all three went up, and the other five happened to round up too.
Three pence sounds like nothing. But the demands are a ledger, and a ledger that does not agree with the bill fails every reconciliation after it.
Scenario: Enter =$D$11*B2/$B$10 in E2 and fill it down, then format E2:E9 to four decimal places. Check that E6, E7 and E8 end in 0.0050, the half-pennies that ROUND lifts.
04Decimal Places Hide Pennies, Not Remove Them
A tempting shortcut is to leave column E unrounded and format it to two decimals. The column then totals £12,850.00 exactly, because the hidden fractions add up.
But the letters print 1,124.38 for Flats 5 and 7, and the leaseholders pay what is printed. The pennies the format hid are the same 3p, collected at the bank instead of on the sheet.
File > Options > Advanced > Set precision as displayedrounds every stored value to its format. It changes the whole workbook permanently and cannot be undone, so it is not a fix for one column.
Scenario: Format E2:E9 to two decimal places and enter =SUM(E2:E9) below them. Check that it shows 12,850.00, then type the eight figures you see into another column and sum those instead.
05Round the Running Total, Then Take the Difference
The fix is to round where the shares accumulate, not row by row. Round each running total to the penny, and charge each flat the step from the previous one. In G2, filled to G9:
=ROUND($D$11*SUM($B$2:B2)/$B$10,2)-SUM(G$1:G1)
SUM($B$2:B2) is the area so far, so the first part is the bill owed by everyone up to this flat. SUM(G$1:G1) is what has already been charged above. SUM skips the text header in G1, so the first row needs no special case.
The last running total is the whole area, so it rounds to the whole bill. Column G adds up to £12,850.00 by construction, whatever the areas.
| Flat | Exact share | Rounded share | Balanced demand |
|---|---|---|---|
| Flat 2 | 2,019.2857 | 2,019.29 | 2,019.28 |
| Flat 5 | 1,124.3750 | 1,124.38 | 1,124.37 |
| Flat 7 | 1,124.3750 | 1,124.38 | 1,124.37 |
Three flats pay a penny less than ROUND gave them. Every flat is still within a penny of its exact share. The order of the rows decides which flat takes a penny, so sort the list the same way each quarter.
Scenario: Enter =ROUND($D$11*SUM($B$2:B2)/$B$10,2)-SUM(G$1:G1) in G2 and fill it to G9. Check that G6 returns 1,124.37 and that =SUM(G2:G9) returns 12,850.00.
06Plugging the Last Row Works, at a Cost
The other common fix is a balancing figure. Round every row except the last, and charge the last row whatever is left:
=$D$11-SUM(H2:H8)
It balances, but every row's rounding lands on one flat. Flat 8 is charged £1,629.17 against an exact £1,629.1964, nearly 3p under, while the others carry their own rounding.
On eight flats that is pennies. On a 400-line cost allocation, the last line collects 399 rows of rounding, and the cost centre that happens to sort last pays for it. The running total keeps every row within a penny.
A third method, largest remainder, rounds every share down and hands the leftover pennies to the rows with the biggest fractions. It is the fairest split, but it needs a ranking column and gives the same totals as the running total.
Scenario: Enter =ROUND($D$11*B2/$B$10,2) in H2:H8 and =$D$11-SUM(H2:H8) in H9. Check that H9 returns 1,629.17, then compare it with E9.
07Equal Instalments Need the Same Trick
Splitting one amount into equal payments is the same problem with equal weights. =ROUND($D$11/3,2) gives £4,283.33, and three of those come to £12,849.99.
Number the instalments 1 to 3 in A14:A16 and use the running total again:
=ROUND($D$11*A14/3,2)-ROUND($D$11*(A14-1)/3,2)
| Instalment | Plain ROUND | Running total |
|---|---|---|
| 1 | £4,283.33 | £4,283.33 |
| 2 | £4,283.33 | £4,283.34 |
| 3 | £4,283.33 | £4,283.33 |
| Total | £12,849.99 | £12,850.00 |
The penny moves to the middle instalment because that is where the running total first rounds up. Change the 3 to 12 for monthly payments.
Scenario: Enter 1, 2 and 3 in A14:A16 and the formula above in B14, then fill it to B16. Check that B15 returns 4,283.34 and that =SUM(B14:B16) returns 12,850.00.
08Check the Split Before It Goes Out
Two checks catch both failures this sheet had. The first asks whether the demands add back to the bill:
=ROUND(SUM(G2:G9)-$D$11,2)
It must return 0. Pointed at column D it returns 25.7, and at column F it returns 0.03. The ROUND stops a binary tail like 0.0000000001 from failing a correct split.
The second asks whether any one flat is unfair:
=MAX(ABS(G2:G9-E2:E9))
It returns 0.0057 for the running total, under a penny. On the plugged column it returns 0.0264, and on the old column 6.425, the overcharge on Flats 5, 6 and 7.
Scenario: Enter both checks above the demands, then point them at D2:D9. Check that the first returns 25.7, then switch them to G2:G9 and check that it returns 0.
Try it in the grid09Five Traps
- Rounding a percentage, then multiplying. Every rounded weight is wrong by a little, and the bill is multiplied by all of them. Round the money, never the weight.
- Rounding each row and trusting the total. Eight correct roundings still added up to 3p over. Round the running total instead.
- Hiding the pennies with a format. Two decimal places show what the letters print, not what the sheet adds up.
- Typing a penny over the biggest share. A hand-typed
2088.12balances this quarter and is wrong the moment the bill changes. - Re-sorting the list between quarters. The running total gives the stray penny to whichever row crosses the half. Keep the order fixed.
10Four Exercises
- Reproduce it. Build the sheet with rounded percentages and confirm the demands total £12,875.70.
- Find the pennies. Switch to
ROUNDon each share and confirm the total is £12,850.03. - Balance it. Add the running-total column and confirm it totals £12,850.00 with every flat within a penny.
- Split it. Spread £12,850.00 over 12 monthly instalments and confirm they add back to the bill.
What to Take Away
The areas were right and so was the bill. The sheet rounded the weights before the money, and then rounded eight shares that all went the same way.
Charge from the areas, round the running total, and check the sum and the worst row before the demands go out.