View Single Post
  #2   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default Sum - Sum < Zero ??? How is it? Bug in Excel formula?

wrote...
....
As respondants in both threads have pointed out, this is a well
known property of finite precision arithmetic exacerbated by
binary representation of numbes. It is not a bug and is not
unique to Excel.


Well, it's not a bug, but a "feature".


Feature in the sense that it's an UNAVOIDABLE aspect of the HARDWARE
to which ALL finite precision software applications that uses that
hardware are subject.

Where in the hell is this "feature" explicited at help system ?
Not so easy to find...


Anyone with any experience in numeric programming is aware of it.

And of course, for a common user, a number is a number, and
nothing more. . . .


Numbers as a mathematical concept are NEITHER FULLY NOR EXACTLY
supported on computers. The 'numbers' computers provide are
equivalence classes on a bounded subset of real numbers. Arithmetic on
these equivalence classes mostly duplicates mathematical arithmetic on
real numbers (within computer bounds) except near those equivalence
classes' boundary points. Then all bets are off.

If you don't like this, try to find other software that more nearly
meets your expectations. And good luck finding it!

If it's typed at 2 decimal precision, it could be good that sum
and multiplycation of it could be also at those 2 single decimal
precision. No use of thinking about IEEE, 15 limit precision,
binary internal representation, and so on... Unless the user ask
for it.

....

You could always use the Precision As Displayed option, but it causes
other problems. But Excel provides ONLY two options in this regard:
IEEE double precision reals (basically Excel's 15 decimal digit
precision reals) and fixed point (Precision As Displayed). Choose the
one you want.