Sum - Sum < Zero ??? How is it? Bug in Excel formula?
 

Please do not separately post the same question to different newsgroups.

Oh, sorry for this. Never more I will do it.

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".
Where in the hell is this "feature" explicited at help system ? Not so
easy to find...

And of course, for a common user, a number is a number, and nothing
more. 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.

I tried the solutions and as trunc() worked well, I put it everywhere.
Now the calculations take some time (few miliseconds more). Also the
parens () works well, but I can not trust 100%.

Well, thanks anyway.

yours, Daniel
from Brazil

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.

Sum - Sum < Zero ??? How is it? Bug in Excel formula?
 

On 12 abr, 07:41, "Harlan Grove" wrote:

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


Yes, I tried it in Delphi using double variables and the values
(115.00 , 113.20 , 1.80) and also got the same result (-2.88658E-15).
Of course, if I was to make a program, and not a excel worksheet, I
would use Currency or Longint types (this last divided or multiplied
by 100), or if going to use real/float types, all the time ensure the
correct round() or trunc() functions.

But this post I included not to talk about programming language or
advanced computation.

I'was with a problem using Excel: some numbers, 2 digit precision,
after sum and subtraction, results different from zero, where the only
value expected was zero.

And now, searching this forum, I found other posts about this same
affair.


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.

I tried it but didn't get the results. As the final user could change
that option, it was better to lost some milliseconds at more trunc()
functions.


Thanks,
Daniel