Excel Total Off by a Cent: Fix Line Sums and Splits
Fix an Excel or Google Sheets total that's off by a cent by rounding invoice lines correctly, comparing tax methods, and balancing rounded payment splits.
On this page
Your invoice lines look like they add to $153.79, but SUM shows $153.78. Nothing is missing: the two-decimal format hides fractions of a cent, and SUM still adds those stored fractions.
Enter this in A1:D8.
| Item | Qty | Unit price | Discount |
|---|---|---|---|
| USB-C cable | 3 | 12.35 | 0% |
| A5 notebook | 5 | 4.79 | 5% |
| Packing tape | 2 | 6.55 | 10% |
| Desk mat | 1 | 18.90 | 5% |
| Printer paper | 4 | 7.25 | 10% |
| Gel pens | 6 | 2.45 | 5% |
| Shipping labels | 3 | 8.95 | 10% |
In E1 type Net line. In E2 enter =ROUND(B2*C2*(1-D2),2) and fill it down through E8. E2 returns 37.05. The seven stored amounts are then 37.05, 22.75, 11.79, 17.96, 26.10, 13.97, and 24.17, and =SUM(E2:E8) returns 153.79, matching the lines you see.
What the unrounded line formula actually stores
Without ROUND, =B2*C2*(1-D2) filled through E8 stores 37.05, 22.752499999999998, 11.79, 17.955, 26.1, 13.965, and 24.165. Formatted to two decimal places, those cells still show 37.05, 22.75, 11.79, 17.96, 26.10, 13.97, and 24.17. Add the displayed figures and you get 153.79.
=SUM(E2:E8) on the unrounded values returns 153.7775, which a two-decimal format shows as 153.78. =ROUND(SUM(E2:E8),2) also returns 153.78: it rounds the combined total, not each line, so the one-cent disagreement remains.
I’d rather round in the line formula, because a later formula that points at E3 then receives 22.75, not 22.7525. That is the right fix when each line is a payable amount to the nearest cent. Some calculations should keep extra precision until the end; an invoice built from separately rounded line charges is not one of them.
ROUND, ROUNDUP, and ROUNDDOWN are not interchangeable with money. The differences are in Excel ROUND vs ROUNDUP.
When you can’t change the existing line formulas
If E2:E8 come from an export and you cannot edit them, leave those formulas alone and total the already-displayed cents.
In this Google Sheets test, =SUMPRODUCT(ROUND(E2:E8,2)) returned 153.79. So did =SUM(ARRAYFORMULA(ROUND(E2:E8,2))). =SUM(ROUND(E2:E8,2)) returned #VALUE! and prompted for ARRAYFORMULA. I did not re-run those in Excel; if I needed the same total there without depending on dynamic arrays, I’d use SUMPRODUCT.
If the gap is much larger than a cent, the amounts may be text. SUM skips text-stored numbers in a range and quietly returns a short total. Converting text to numbers in Excel covers that separately.
Tax on each line and tax on the subtotal can both be right
ROUND will not remove this next one-cent difference, because the two methods are different calculations.
Keep the rounded net amounts in E2:E8. Enter Tax rate in H1 and 8.25% in H2. In F1 type Line tax, and in F2 enter =ROUND(E2*$H$2,2). F2 returns 3.06. Filled through F8, the taxes are 3.06, 1.88, 0.97, 1.48, 2.15, 1.15, and 1.99, and =SUM(F2:F8) returns 12.68.
Tax calculated once on the rounded subtotal, =ROUND(SUM(E2:E8)*$H$2,2), returns 12.69.
Both follow the rounding they claim. Which one an invoice should use is a business or tax-rule decision, not a spreadsheet error. Use the same method on invoices, credit notes, and reports, or the one-cent gap will keep coming back.
Split $1,000 three ways without losing a cent
Three equal shares of $1,000.00 are $333.333333… Rounding each share on its own gives $333.33, and =3*ROUND(1000/3,2) returns 999.99. One share has to take the leftover cent.
Enter this in A12:C15, leaving the Share cells empty, then type Amount to split in D12 and 1000 in D13.
| Recipient | Weight | Share |
|---|---|---|
| Ava | 1 | |
| Ben | 1 | |
| Cora | 1 |
In C13 enter =ROUND($D$13*B13/SUM($B$13:$B$15),2). It returns 333.33. Copy it to C14, which also returns 333.33. In C15 enter =$D$13-SUM(C13:C14), which returns 333.34. =SUM(C13:C15) returns 1000.
The last row absorbs the remainder, which is fine for a one-off split. For a recurring payout, always giving the extra cent to the same person can stack up, so rotate the last row or assign leftover cents by a stated rule.
The same last-row formula works with percentage weights, but 33.3% is not one third. With weights 33.3%, 33.3%, and 33.4% of 1000, the rounded shares in this test were 333, 333, and 334, because 33.3% of 1000 is 333.
A difference that displays as 0 but does not equal 0
A related mismatch shows up in checks rather than invoice totals. Enter 0.1 in A20, 0.2 in B20, and 0.3 in C20.
In this Google Sheets test, =A20+B20=C20 returned TRUE, and =A20+B20 displayed as 0.3. The stored sum was 0.30000000000000004. =A20+B20-C20 stored 5.551115123125783e-17, which displayed as 0, and =(A20+B20-C20)=0 returned FALSE. An IF test on that raw difference also came back not equal. =ROUND(A20+B20-C20,2)=0 returned TRUE.
After the line-level ROUND fix, =SUM(E2:E8) stored 153.79000000000002 and still compared equal to 153.79. The comparison that failed was the subtracted residual against zero, not every equality test. For a money check, compare the difference after rounding to cents. That tells you whether two totals agree to the penny; it does not fix an invoice that is actually off by $0.01.
I have not re-run the 0.1+0.2 comparison in Excel. Excel also has a workbook-wide setting, Set precision as displayed, that makes calculations use displayed precision. I would not turn it on for this. It changes stored values across the workbook, and turning it off does not bring the extra decimals back. A ROUND formula on the cells that need it is safer.
Discounts in the invoice belong in D as percentages. If D3 contains 5 instead of 5%, =B3*C3*(1-D3) returned -95.8 in this test, because the formula treats 5 as 500%.