Penny rounding breaks and sign flips when reconciling in Excel™
Two systems agree on an invoice, but one amount reads 120.00 and the other 119.99. Or one books a credit as negative and the other as positive. (Figures here are illustrative.) A strict equality check calls both a break.
Two causes of false breaks
- Rounding. Each system rounds at a different step, so totals drift by a penny.
- Sign convention. A credit note appears as -45.00 in the ledger and 45.00 in the bank export.
Tolerance by hand
These steps apply to Excel™ in Microsoft 365. Use helper columns after you have matched on a key.
- For rounding, compare with a tolerance rather than equality:
=ABS(B2-C2)<=0.01. Choose a tolerance you can defend, and write it on the sheet. - For sign, compare absolute values:
=ABS(B2)=ABS(C2). Then check the totals still add up. If they do not, the flip is hiding a real difference. - Keep the raw difference column too, so that a tolerance never erases evidence.
- Report breaks above the tolerance and list the tolerated ones separately, so a reviewer can see what was waved through.
A tolerance is a decision. Record who chose it and why.
Let the report show both
Two-File Reconciliation lets you mark a column as a compare column with a tolerance (a penny, or a tenth of a percent). If you tick sign flip, rows that agree after the flip are matched and the totals still add up. Every normalisation switched on is listed on the Audit sheet. Free for up to 1,000 rows a side with a single key column; the demonstration in the guide uses made-up figures.
See Two-File Reconciliation for Excel™
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Two-File Reconciliation is not affiliated with or endorsed by Microsoft.