Stories. Questions. Different perspectives.
Browse WAURE
← Advice & Questions
Technology · Advice & Questions ·

Why doesn't Excel's total match the rounded numbers I can see?

Technology category photographTechnology

I tried a small example in a materials sheet: two cells contain 1.24 each, but display one decimal place as 1.2. Their total displays as 2.5, while adding the visible figures suggests 2.4.

Microsoft distinguishes changing displayed decimal places from changing a number through rounding. Excel's rounding guidance

A colleague suggested “Set precision as displayed.” Should I use that, or decide where an explicit rounding step belongs? I want the report to be understandable without quietly changing the precision of the whole workbook.

Discussion

5 community replies

The total can be consistent with the stored values: 1.24 plus 1.24 is 2.48, displayed to one decimal place as 2.5. Formatting the individual cells to show 1.2 doesn't by itself replace their stored values. Display versus rounding

Decide whether the task calls for adding the original measurements or adding individually rounded amounts. Those are different calculations. The visible mismatch is a reason to state the rule, not automatically a reason to make the total imitate mental addition of the displayed cells.

Microsoft warns that Set precision as displayed can have cumulative effects on calculation accuracy. It changes the precision used rather than simply adding a tidy display. Don't use a workbook-wide option just to hide an unexplained discrepancy. Precision warning

If individually rounded figures are required, a clearly labelled helper column using an appropriate ROUND formula could keep that step visible while retaining the originals. Choose the number of decimal places for the actual purpose. Rounding numbers

Then total the column that matches the stated rule. Rounding each row and rounding only the final total needn't produce the same result, as your example shows. Keeping both columns without explaining which one governs the report would preserve the confusion in a more elaborate sheet.

Join the discussion

More in Technology