You can see the impact of accumulating binary approximations by copying
cell C14 and Edit|Paste Special|Values you will see that the value is
50297.3600000001, instead of 50297.36. The sum in C7 is not visibly
different from 28853.09, but =(C7-28853.09) shows that even here you
have a small difference from what you expected.

The issue is not the SUM function per se (the math is correct), but
unavoidable approximations to the inputs. When you do math with
approximate inputs, it should be no surprise that the result is only
approximate. Almost all decimal fractions have no exact binary
representation (just as 1/3 has no exact decimal representation). It is
also worth noting that this applies to almost all computer software;
Excel is representing numbers as specified in the IEEE standard.

The original question was why
=IF(C19=C28,0,"ERROR")
returns "ERROR" instead of 0. I cannot reproduce this result given the
formulas specified for C22 and C28, and the posted description that
C20:C21 and C23:C27 are blank (so that C28 is equivalent to =C22 and C22
is equivalent to =C19. Possibly one or the other of C22 and C28 was
hand enetered instead of calculated by the posted formulas? If C19:C28
were really as posted, what version of Excel was used?

Jerry

Ron Rosenfeld wrote:

On Fri, 8 Jul 2005 15:24:05 -0500, ddelong
wrote:


Yes I had a typo in my sum, it should've been sum(c10:C13) good catch
!

Thank you for the explanation but it still doesn't explain why I could
use other numbers to add up to 28853.09 and be fine or even simply type
28853.09 in that field and not use the sum function.

Should I use the round function EVERY time I use the sum function so
that this can't happen ?

If someone could elaborate I would appreciate it.


Jerry W Lewis writes about this frequently. And yes, if you are interested
that two separate SUMS equate exactly to zero, you will either have to use the
ROUND function, or use INTEGER math.

Here is one of Jerry's previous postings about a similar issue. The concept is
applicable to your problem:

===========================
the easiest way to think about these issues is to recognize that
Excel's documented limit of 15 decimal digit accuracy relates to the
accuracy of decimal/binary conversion. Thus your problem can be thought
of as
-30584.0100000000?????
30585.6000000000?????
----------------------
1.5900000000?????
which is consistent with your result of
1.59000000000015

The binary approximation to 30584.01 is 8406868654754365/274877906944
The binary approximation to 30585.6 is 4203652855313203/137438953472
The exact difference between these numbers is 437055872041/274877906944
which in decimal is 1.59000000000014551915228366851806640625, or
1.59000000000015 to 15 figures.

In keeping with Excel's documented limit of 15 decimal digit accuracy,
if you ask for more than 15 decimal digits, the trailing digits are
arbitrarily displayed as zero.

Jerry
===================================
--ron