Re: Q: When 0 <> 0 ?
- From: joeu2004@xxxxxxxxxxx
- Date: 27 Oct 2005 14:23:09 -0700
splever@xxxxxxxxx wrote:
> 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.
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...
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.
.
- References:
- Q: When 0 <> 0 ?
- From: splever
- Q: When 0 <> 0 ?
- Prev by Date: Re: Closing Excel 2000 from VB (Unwanted process EXCEL.EXE)
- Next by Date: Re: Stumped
- Previous by thread: Re: Q: When 0 <> 0 ?
- Next by thread: Re: Q: When 0 <> 0 ?
- Index(es):
Relevant Pages
|
