Q: When 0 <> 0 ?

S

splever

A: When you add it in excel!

Example: Place the following values in a column and sum them.

-116.33
98.53
17.80
0
=============
Excel returns: 0.0000000000000035527136788005

Correct Answer: 0

If you remove 0 from the list ( or put it in a different place in the
list, the proper result, 0 is returned.

I assume this has to do with the known floating point issues, but I've
never seen an example where 0<>0, so I thought I'd share.

I have a workaround, but would love to see comments...
 
J

joeu2004

Example: Place the following values in a column and sum them.
-116.33
98.53
17.80
0
=============
Excel returns: 0.0000000000000035527136788005
[....]
If you remove 0 from the list ( or put it in a different
place in the list, the proper result, 0 is returned.
Click to expand...

I do not think that any of the answers so far address
your point, namely: the result is order-sensitive. Even
taking the IEEE floating-point format into account, it
does seem odd that adding zero at different times yields
different results.

I think the answer goes beyond the IEEE FP format to the
typical IEEE FP implementation within the CPU or chipset.
As I recall, for Intel chips, the internal FP format is
twice as wide as the storage. Programmers can rely on
the internal FP registers explicitly -- and compilers
can use them in various ways, depending on the complexity
of the computation. This can lead to different truncation
errors, depending on when the wider internal FP results
are copied into memory or general-purpose FP registers.
I have a workaround, but would love to see comments...
Click to expand...

Since the behavior is relatively unpredictable and
potential non-portable across processors, it would be
better to live with the reality and use prudent numerical
methodologies to deal with it.

For example, never compare a floating-point result of a
computation with zero, and never compare two FP results
for equality. Always compare with some "delta", the
size of which depends on your requirements. For example:
=IF(ABS(A1-A2) < 1E-6, "equal", "not equal").

Alternatively, when feasible, only use integral
floating-point values, using ROUND() for FP results.

Of course, that is easier said than done. Most of us
dispense with the "prudent" methods and hope for the
best.
 
J

Jerry W. Lewis

Bob, Chip and John have correctly explained why 3.55E-15 is the correct
answer, which leaves only the order of operations part of your question.
Joe is mistaken that "the behavior is relatively unpredictable and
potential non-portable across processors". There is a perverse
consistency that has been repeatedly discussed in these newsgroups.

Because most decimal fractions cannot be exactly represented in finite
precision binary, with Excel 97 MS introduced a fuzz factor into
addition/subtraction to try to avoid such questions
http://support.microsoft.com/kb/78113?#XSLTH3196121122120121120120
I agree with Kahan
http://www.cs.berkeley.edu/~wkahan/Mindless.pdf
that introducing this fuzz factor has avoided an easy question at the
expense of much more difficult and confusing questions.

When the final arithmetic operation is effectively a subtraction between
numbers that agree to 15 decimal digits, then Excel will arbitrarily
return zero instead of the exact binary difference, on the assumption
that the difference is the residual of binary approximations. Thus
=-116.33+98.53+17.8
will return 0 on all processors, and
=(-116.33+98.53+17.8)
will return 3.55E-15 on all processors. The difference is that in the
second form the parentheses are viewed as the final operation, instead
of the sum of
-17.7999999999999971578290569595992565155029296875
+17.800000000000000710542735760100185871124267578125

Specifically, the binary approximations to the numbers involved
translate back to decimal as
-116.3299999999999982946974341757595539093017578125
98.530000000000001136868377216160297393798828125
17.800000000000000710542735760100185871124267578125
---------------------------------------------------
0.000000000000003552713678800500929355621337890625
but you can predict the potential size of binary approximation residuals
by using Excel's documented limit of 15 decimal digits, thus you can
view your problem as
-116.330000000000???
98.5300000000000??
17.8000000000000??
--------------------
0.000000000000???
which is consistent with Excel's answer of
0.000000000000003552713678800500929355621337890625
When it is appropriate to round as Joe suggested, then this way of
thinking about the problem may help you to know how many decimal places
you can reliably keep.

Jerry
 
D

Dana DeLouis

Hi. For the op, I believe that the number you see is the translation of a
lone bit at the 48th place into base 10.
=POWER(2,-48)
0.000000000000003552713678800500
 
H

Harlan Grove

Wild tangent.

Note that QUOTIENT(3,-5) returns -0, and =ABS(QUOTIENT(3,-5)-0)=0 returns
TRUE but =QUOTIENT(3,-5)=0 returns FALSE. If Excel complied with IEEE 754,
then the latter formula should also return TRUE, so while 0 <> 0 isn't
really the case in Excel, -0 <> 0 is.
 
Top