problem with Summation
 

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

problem with Summation
 

Maybe this:

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

or this:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"walrus" wrote in message
...
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

problem with Summation
 

"walrus" wrote:
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,996447286321199499070644378662109 375,
and 0.1 is stored exactly as
0.100000000000000,00555111512312578270211815834045 41015625.

(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 -----

"walrus" wrote in message
...
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