Rounding Effecting Sum

[Steve Graves]

I have cells that cotain formulas based on adding four other cells together.
One of those cells is the result of a percentage applied to a dollar amount.
While it only displays 2 decimal places when looking at it, the formula seems
to recognize that there are more. This is interfering with the total in one
of my columns. The dollar amounts don't add up correctly because the sum is
adding decimal places I am not seeing.

So. How do we shave off those places so the formula doesn't see them?

 

[Bob I]

See "Set rounding precision" in Excel Help.

 

[Joe User]

While it only displays 2 decimal places when looking at it, the formula
seems to recognize that there are more.

Correct. Formatting normally only affects what is displayed, not the
underlying actual value.

How do we shave off those places so the formula doesn't see them?

I believe the best way is to use ROUND prolifically. For example, in the
cell involving a percentage calculation:

=ROUND(A1*B1, 2)

But even if you round the computation in every cell, it would be prudent to
round their sum:

=ROUND(A2+B2+C2+D2, 2)

This avoids another source of unexpected results due the way that Excel (and
most applications) store numbers and perform arithmetic, called binary
floating point. For example:

=IF(10.1 - 10 = 0.1, TRUE)

results in FALSE(!). This can be corrected using:

=IF(ROUND(10.1 - 10, 1) = 0.1, TRUE)


PS: An alternative to using ROUND everywhere is to use the calculation
option "Precision as displayed". I do not recommend it for a number of
reasons. If you want to experiment with PAD, be sure to copy your Excel
file first. PAD can make unintended changes that are irreversible.


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