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

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.

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

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. :-)
--
David Biddulph


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

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Sam" wrote:

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

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 a -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" wrote:

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. :-)
--
David Biddulph


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

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.
--
David Biddulph

Sam wrote:
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
a -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" wrote:

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. :-)
--
David Biddulph


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

On Feb 23, 12:08 pm, Sam wrote:
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 a
-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,071054273576010018587112426757812 5 and
0.160000000000000,00333066907387546962127089500427 24609375. (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,987565502124198246747255325317382 8125 .
(-8.55+0.16)+(161-150) is
2.60999999999999,94315658113919198513031005859375 .



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


On Feb 23, 12:08*pm, Sam wrote:
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 a *-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" wrote:
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. *:-)
--
David Biddulph

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

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