decimal values create inequality

[Sam]

I have an error-checking formula in a spreadsheet:
"If(Sum(A1:A30)<>D1,"ERROR","Balanced"), where A1:A30 are values that are
entered from a source document, while D1 is the Total listed on the same
source document. The purpose is to ensure that all component parts are
correctly entered. All values are formatted as #,##0.00.

There are random times whereby the sum of A1:A30 results in a number such as
25.0000000001 which does not equal the Total of 25 that had been entered
into D1. Since the above formatting displays 25 for both values, the only way
of combatting the issue is to use a Round function for the Sum.

How can this happen when there is no division involved and all components
are entered as dollars and cents?

Excel version 2003.

Your help is greatly appreciated,

Sam

 

[Fred Smith]

This is common with computers. They work in binary, we work in decimal. The
common solution is to round your numbers to the precision required -- in
your case 2 decimal places. So use:
=If(round(Sum(A1:A30),2)<>D1,"ERROR","Balanced")

Regards,
Fred.

 

[David Biddulph]

If your number of 25.00 is formed by adding 12.40 to 12.60, you can find the
source of the problem by calculating the *exact* binary representation of
0.40, for example.
Come back to us when you've done it.

 

[Shane Devenshire]

Hi,

Computers work in binary, we work in decimals which results in
approximations by Excel and any computer.

Here is everything you need to know about this issue (and more):

http://support.microsoft.com/kb/78113/en-us
http://support.microsoft.com/kb/42980
http://support.microsoft.com/kb/214118
http://www.cpearson.com/excel/rounding.htm
http://docs.sun.com/source/806-3568/ncg_goldberg.html

there are innumerable solutions but you alreay have one.

 

[Sam]

Thanks for your response David. I'm not sure what you mean by "calculating
the exact binary representation of .40". This formula has produced a
"balanced" flag every day for over 7 months and now found the inequality.
Actually, the numbers that are being summed are: -8.55, .16, 161.00,
-150.00. The actual sum of these numbers is 2.61. When the user entered 2.61
in the check total field the formula matching the two numbers showed an
inequality because the sum function produced a value of 2.61000000001 which
was not apparent because it was formatted as 2 decimal places.

I have seen this happen on rare occasions before. There seems to be no
apparent cause. It doesn't seem reasonable that I should have to use the
Round function every time I want to compare the sum of components to their
total.

 

[David Biddulph]

1100 binary is equivalent to 12 in decimal
1100.01 is 12.25
1100.011 is 12.375
1100.011001 is 12.390625
1100.0110011001 is 12.3994140625

Keep trying to add extra digits and see whether it ever becomes exactly
12.40.
The answer is that it doesn't.

It's rather like the fact that you can't represent 1/3 or 1/7 as a finite
decimal number.

If you want things to work out correctly, you'll either need to use the
ROUND function, or work in integers by calculating in cents instead of in
dollars.

 

[joeu2004]

It doesn't seem reasonable that I should have to use
the Round function every time I want to compare the
sum of components to their total.

But unfortunately, you do. Well, it is either that or one of two
other approaches, each of which has its detractors. One alternative:
never compare for equality; for example, IF(ABS(SUM(A1:A30)-D1) >=
0.005,"ERROR",...). Another alternative: use the "Precision as
displayed" option under Tools > Options > Calculation.

Actually, the numbers that are being summed are:
-8.55, .16, 161.00, -150.00. The actual sum of these
numbers is 2.61.

What people have been trying to explain to you is: -8.55 and 0.16
cannot be represented exactly in the internal binary representation
used by Excel (and most applications). Instead, they are
-8.55000000000000,0710542735760100185871124267578125 and
0.160000000000000,0033306690738754696212708950042724609375. (The
comma demarcates 15 significant digits to the left.)

But that is not the full story. The rest of the explanation goes far
beyond this tutorial in binary representation. In a nutshell, it has
to do with the magnitude of the pairwise numbers and intermediate
results involved in the computation. For example, whereas SUM
(-8.55,0.16,161,-150) does not exactly equal the internal
representation of 2.61, SUM(-8.55,0.16)+SUM(161,-150) -- aka
(-8.55+0.16)+(161-150) -- is close enough to 2.61 [1] that Excel
considers them to be equal.

The point here is: you can find many examples where you get away with
not rounding before comparison; but you can also find many examples
where you do not get away without it. This mystifies almost everyone;
even those of us who understand the computer science of it get bitten
by this from time to time.

HTH.


Endnotes:

[1] 2.61 is 2.60999999999999,9875655021241982467472553253173828125 .
(-8.55+0.16)+(161-150) is
2.60999999999999,94315658113919198513031005859375 .



----- original posting -----

 

[Bernd P]

Hello Sam,

I grew up with the saying "never compare a floating number to zero",
so I would suggest
"If(ABS(Sum(A1:A30)-D1)>1E-14,"ERROR","Balanced")

Regards,
Bernd