Excel formula adding error

Question

Using Excel I have a adding error. I think this may be a floating point error or a rounding error. When I add a cell with dollars but 0 cents value to an existing number the result is off by one cent in two different cells. They offset each other but the rows between the two totals are correct.

Answer 1

The behavior is caused by how Excel stores and calculates floating‑point numbers, not by a logic error in the formulas.

Excel uses IEEE 754 double‑precision floating‑point. Many decimal values (including some that display as .00) cannot be represented exactly in binary, so Excel stores a very close approximation. When several such approximations are added and subtracted, the tiny differences can accumulate and show up as a 0.01 discrepancy in some totals, even though intermediate rows look correct. In other places the accumulated error can cancel out, which is why two totals may be off by ±$0.01 but the rows between them are fine.

To eliminate the visible 1‑cent differences in currency calculations, force Excel to round the results that are used in further arithmetic:

1. Wrap the expressions that produce monetary values in ROUND, for example:
   • =ROUND(original_formula, 2)
   • or for a sum: =ROUND(SUM(range), 2)
2. Use these rounded results in subsequent formulas (totals, running balances, etc.).
3. Alternatively, enable Precision as displayed (File → Options → Advanced → “Set precision as displayed”) so Excel stores numbers rounded to the number of decimal places shown. This changes stored values for the entire workbook and can permanently alter data, so it should be used only when this behavior is acceptable.

These approaches align the stored values with the two‑decimal currency display and prevent the 0.01 offsets from floating‑point rounding.

---

References:

• Floating-point arithmetic may give inaccurate results in Excel