Split a shared cost across units, round each share to the nearest cent, and the shares will not add up to the bill. This is not a fault in your spreadsheet. It is arithmetic, and it turns up whenever the division does not come out even.
Take a $1,000.00 water bill across seven units, split equally. The exact share is $142.857142... Round each up to $142.86 and the column sums to $1,000.02, two cents nobody paid. Round each down to $142.85 and it sums to $999.95, five cents of a real invoice assigned to nobody.
Two cents is not a crisis. The habit is.
Where the gap shows up
Anywhere one number gets divided among several parties. Utility chargebacks split by meter count or square footage. Operating cost estimates spread across a floor plate. A repair invoice apportioned across three owners. Deposit interest divided between joint tenants. Tax applied to a line that was already prorated. The larger the denominator, the larger the gap: each share can be off by up to half a cent, and those halves lean the same way when you always round in the same direction.
Allocate the remainder, do not round it
Stop treating each share as an independent number. Treat the set as one total that has to be handed out completely.
- Compute every share at full precision. No rounding yet.
- Round every share down to the cent. The column is now short of the invoice.
- Subtract that short column from the invoice total. The difference is a whole number of cents.
- Rank the units by the fraction you dropped in step 2, largest first.
- Give one extra cent to each unit down that ranking until the difference reaches zero.
The column now equals the invoice exactly, and no unit is more than a cent away from its true share. This is the largest remainder method, the same rule used to turn vote shares into whole seats.
Decide the tiebreak before you need it
In the water bill above, every share is identical, so every remainder is identical and the ranking cannot break the tie for you. Five cents, seven units, no natural order. Pick a rule and write it into the procedure: lowest unit number first, largest unit first, or a rotation that moves the starting point each period.
Rotation is the one worth the effort. Otherwise the same two units absorb the extra cent twelve times a year, and a resident who adds up their statements will be right to ask.
Round once, at the end
Every rounding step is another chance to shed a fraction, so do not stack them. If you prorate a rent, apply a percentage, then split the result, round after the last operation, not after each one. Carry full precision through the middle of the chain even when the intermediate figures look untidy on screen.
While you are in there, find out how your tools break a tie on an exact half cent. They do not all answer the same way. Python rounds a tie toward the even number, so round(0.5) returns 0 and round(1.5) returns 2. Plenty of other tools round a tie away from zero and return 1 and 2. Run the same half value through your spreadsheet and your accounting system before you reconcile one against the other.
The check to add
One line on every allocation you produce: does the allocated column equal the source invoice, to the cent? Make it a formula that returns the difference rather than two numbers you compare by eye. A zero means the money is fully assigned. Anything else means you are about to bill, or absorb, a cent that belongs to a specific unit.
A divided invoice has to add back up to the invoice. The leftover cents belong somewhere particular, and your method is what decides where.