Rounding Effecting Sum
 

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?

Rounding Effecting Sum
 

See "Set rounding precision" in Excel Help.

Steve Graves wrote:

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?

Rounding Effecting Sum
 

"Steve Graves" <Steve wrote:
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 -----

"Steve Graves" <Steve wrote in message
...
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?