ROUND in Excel: why rounded rows can disagree with the total

Start with three amounts that expose the difference

A materials estimate contains three small charges. Each charge is 2.334 units. Put the descriptions Sample A, Sample B and Sample C in A2:A4, and enter 2.334 in B2:B4. Keeping the amounts identical makes the rounding difference easy to check without a calculator.

The unrounded total is 7.002. Each amount rounded to two decimal places is 2.33. Three amounts of 2.33 add up to 6.99. Both calculations are correct; they apply rounding at different points.

Round each row when the row amount is the value you need

In C2, enter the formula below and copy it to C3 and C4. The first argument is the amount; the second asks for two digits after the decimal point. The three results are 2.33, 2.33 and 2.33.

Add C2:C4 to obtain 6.99. This approach makes the total agree with the rounded row amounts. Whether that is the required method depends on the rules of the document you are preparing.

=ROUND(B2,2)

Round the total when the original amounts must be accumulated first

This formula first adds the original values in B2:B4, then rounds their sum. The intermediate sum is 7.002, so the result is 7.00, displayed as 7 if the cell uses a general number format.

The difference from the rounded-row total is 0.01. Do not patch a row to conceal it. Choose where rounding belongs, document that choice, and use it consistently. This example explains arithmetic, not a tax or invoicing rule.

=ROUND(SUM(B2:B4),2)

Showing fewer decimals does not replace ROUND

Formatting B2 to show two decimals can make it look like 2.33 while its underlying value remains 2.334. A formula that adds B2:B4 still uses those underlying values. Formatting controls the appearance; ROUND produces a new numeric result.

If a displayed total seems not to match the visible rows, temporarily show more decimals in the input cells. This often reveals the hidden fractions. Keep the original inputs when you need to inspect or recalculate the estimate later.

Check negative amounts at the midpoint

At an exact halfway point, worksheet ROUND rounds away from zero. For example, -2.5 rounded to a whole number becomes -3, and 2.5 becomes 3. A credit or correction can therefore expose a rounding mistake that positive-only examples miss.

This is worksheet ROUND. Do not assume that a programming-language rounding function or a VBA routine has identical tie-breaking behaviour.

=ROUND(-2.5,0)

Use negative digits to round to tens or hundreds

A digits argument of 0 asks for a whole number. A negative argument moves the rounding position to the left of the decimal point: -1 for tens, -2 for hundreds. Rounding 125 to tens gives 130; rounding -125 to tens gives -130.

Choose the position from the meaning of the output. An approximate capacity figure may use tens, while a unit price may need more than two decimals. Two decimal places are not a universal rule.

=ROUND(125,-1)

Try a change that removes the discrepancy

In the practice table, change B4 from 2.334 to 2.336. Rounding the original total now gives 7.00. Rounding each row gives 2.33, 2.33 and 2.34, also totalling 7.00. The two methods do not disagree for every dataset.

Use the formula below to compare the row-rounding approach directly without adding a helper column. For a longer worksheet, a helper column is easier to inspect and copy down. Reset the example to return to the original one-cent difference.

=ROUND(B2,2)+ROUND(B3,2)+ROUND(B4,2)

Further reading

• Microsoft: ROUND function
• Microsoft: round a number and change displayed decimals