Decimal rounding causing addition errors

[TKGerdie]

We have a spreadsheet we use for bidding our jobs. The summary page has 5
line items that pull from another spreadsheet. Each of those cells are
formatted to only show 2 decimal places. However, because one of the items
(materials) is a formula result is has 8 decimal places and then only shows
2. Our customer is confused as to why when he manually adds the figures they
do not match the total on our sheet. Is there any way to fix this?

 

[Jacob Skaria]

Use ROUND function with your formula =ROUND(<formula>,2)

=ROUND(A1,2)

If this post helps click Yes

 

[JoeU2004]

because one of the items (materials) is a formula result is
has 8 decimal places and then only shows 2. Our customer
is confused as to why when he manually adds the figures they
do not match the total on our sheet. Is there any way to fix this?

If I understand you correctly, the problem arises because the underlying
value (with 8 dp) differs from the displayed value (with 2 dp). Formatting
only changes the appearance, not the underlying value.

You need to use ROUND(formula,2) to ensure that WYSIWYG.

However, note that this might also change the results of any formulas that
depend on this cell.

If that is not your intent -- if you need to use the unrounded value in
calculations -- then the simplest solution might be to add a footnote on the
summary page that explains that some values have been rounded in the
presentation, but not in the calculations.

Note: An alternative might be to calculate the exact value in one cell, and
use =ROUND(A1,2) for the summary page only. But that would not solve the
customer's problem, namely: confusion because the presented numbers do not
match the calculation.


----- original message -----