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

[danieldc]

Hi, I just found this "feature" at Excel formulas today.

Please, have a look at: http://danielgudang.multiply.com/journal/item/192
(in portuguese, but images show all)


Let me explain:


some cell C1 = sum (C2:C5)
some cell D1 = sum (D25)
some cell E1 = sum (E2:E5)


A1 = C1 - D1 - E1
B1 = C1 - (D1 + E1)


A1 = B1 ??? Oh, not always!


Sometimes A1 will be +0, sometimes -0.


Really strange!

 

[JE McGimpsey]

It's a feature of any application that uses IEEE double precision
floating point math (e.g., every commercial spreadsheet I know of).

It's the result of having finite precision representation of numbers.
Just as 3 * 1/3 = 3 * 0.3333... will be less than one for ANY finite
number of decimal digits, so will most numbers not be exactly
represented in binary.

Using parens changes the order of calculation, so it's not unusual that
there would be some rounding errors causing a very small remainder.

You can compensate that by using ROUND(), or for setting an error limit,
e.g.:

=ROUND(A1-B1,10) = 0

or
=(A1-B1)<10^-12

both of which will return TRUE for your conditions.

See

http://cpearson.com/excel/rounding.htm

for more.

 

[Guest]

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

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.

The blogger that you cited contributed to his own confusion by using fixed
decimal formatting, which obscured what was really going on. His examples
can be simpified to
=115-113.2-1.8
returns -2.88657986402541E-15 instead of zero, and
=215-213.2-1.8
returns 1.13242748511766E-14 instead of zero

In decimal, these expresions would return zero, but most terminating decimal
fractions are non-terminating binary fractions that can only be approximated,
just as 1/3 can only be approximated as decimal fractions. What is happening
in these examples is comparable to doing 1-1/3-1/3-1/3 as decimal fractions
and not getting zero
1.0000
-0.3333
-0.3333
-0.3333
-------
0.0001
i.e. when you do math with approximate inputs, then it should be no surprise
when the result is also an approximation.

Specifically, the IEEE binary approximations to 213.2, 112.2, and 1.8 have
decimal values of
213.19999999999998863131622783839702606201171875
112.2000000000000028421709430404007434844970703125
1.8000000000000000444089209850062616169452667236328125
If you do the math, you will find that Excel's answers to the example
calculations are correct, given these initial approximations.

For a simpler rule of thumb, use Excel's specifications documented in Help,
where it states that Excel only supports 15 digit math. Thus you can think
of the calculations as
115.000000000000???
-113.200000000000???
-1.80000000000000?
--------------------
0.000000000000???
and
215.000000000000???
-213.200000000000???
-1.80000000000000?

 

[danieldc]

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

 

[danieldc]

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

Ops! 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.

Not a bug? Well, it's a "bad feature" then. The help system could be
nice to inform it, but it's hard to find.

The common user will not interested about internal precision
representation, IEEE, binary bits and so on.
If typed a single number 2 decimal precision, after multiplication and
sum, nothing more than 2 digit precision. Unless he/she asks for it.

Well, I finished using trunc() at every formula and now calculations
take time (some miliseconds more).
Also parens () worked well, but I can not trust 100%.

Thanks, anyway.

yours, Daniel
from Brazil

 

[Bruce Sinclair]

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


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

No it's not a feature either. It's just reality interfering.

As with everything, it's important to know how things work so you can
explain the times when they don't.

 

[Harlan Grove]

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.

 

[danieldc]

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