problem with Summation

[walrus]

Guys

I have come across this problem a number of time. C1 to C7 are all 2 decimal
placed numbers but the summation (=sum(C1:C7) of all of them come out as
332,069.1800000010000. What can i do about this?

Expenses $
C1 6,055,337.25
C2 (611,570.68)
C3 0.00
C4 (5,259,682.31)
C5 1,119.12
C6 146,826.12
C7 39.68
Manual Sum 332,069.1800000010000
Cosolidated 332,069.18
Diff 0.0000000006985

 

[Niek Otten]

Maybe this:

http://www.mcgimpsey.com/excel/pennyoff.html

or this:

http://support.microsoft.com/kb/78113

 

[JoeU2004]

I have come across this problem a number of time. C1 to C7 are all 2 decimal
placed numbers but the summation (=sum(C1:C7) of all of them come out as
332,069.1800000010000. What can i do about this?

Unfortunately, this is not uncommon. The simple answer is: use ROUND
prolifically, but prudently. For example:

=ROUND(SUM(C1:C7),2)

But if any of C1:C7 are formulas, you might want to consider using ROUND
there, too.

To demonstrate how common the problem is, try the following just for fun:
=IF(10.1 - 10 = 0.1, TRUE). It will return FALSE (!).

But =IF(ROUND(10.1 - 10, 2) = 0.1, TRUE) returns TRUE as expected.

This is not a defect. The problem arises because of the internal form
(binary floating point) that Excel and most applications on binary computers
use to represent numbers and perform arithmetic. Most numbers with decimal
fractions cannot be represented exactly. For example, 10.1 is stored
exactly internally as 10.0999999999999,996447286321199499070644378662109375,
and 0.1 is stored exactly as
0.100000000000000,0055511151231257827021181583404541015625.

(The comma is my way of demarcating the first 15 significant digits, which
is all that Excel will display, rounding the 16th significant digit.)


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