"Jerry W. Lewis" wrote:
The characterization 'if the result of the last operation is
"close" to zero' may be misleading.
Agreed. I was merely expanding on the explanation from
http://support.microsoft.com/kb/78113, which states (emphasis added):
"Should an addition or subtraction operation result in a value at or very
__close_to_zero__, Excel 97 and later will compensate".
I should have known better than to think that MS knows what its product does
;-). But I did not want to stray too far from MS's explanation, since I
have no direct knowledge of the specification and implementation of this
heuristic.
The issue seems to be that the two numbers involved in the
final subtraction are identical to 15 significant figures.
I think you are close (no pun intended). But I'm not sure it has to do with
significant digits per se.
You offered one counter-example where the two operands of the last
subtraction are the same to 15 sig digits, yet Excel's heuristic does not
apply.
Consider the following counter-example where the two operands differ in the
first 15 sig digits (format as Scientific with 14 dp), yet Excel's heuristic
does apply.
A1: =2^1023+(2^1023-2^971) ''largest integer
A2: =2^1023+(2^1023-5*2^971) ''nearest integer that differs in first15 sig
digits (displayed)
A3: =A1-A2
A4: =(A1-A2)
A3 is exactly zero. A4 is about 7.98336123813888E+292.
A1 displays as 1.79769313486232E+308, but the first 30 digits of its exact
representation are 179769313486231,57081452742373 (the comma demarcates 15
sig digits to the left). A2 displays as 1.79769313486231E+308, but the
first 30 digits of its exact representation are
179769313486231,49098091504234.
Noting that the first 15 sig digits of the exact representations are the
same, it might be tempting to restate your rule in that way. However, here
is a counter-example to the restated rule.
A1, A3 and A4 are the same formulas as above.
A2: =2^1023+(2^1023-9*2^971) ''nearest integer to A1 that defeats Excel's
heuristic
A3 and A4 have the same internal representation, which is displayed as about
1.59667224762778E+293.
A2 displays 1.79769313486231E+308, the same as before. The first 30 digits
of its exact representation are 179769313486231,41114730266095. A1 and A2
have the same truncated 15 sig digits, like the first example; but A3 is not
zero, unlike the first example.
I have been looking at the binary representations, hoping to find a
consistent rule that might, in part, explain exceptions to your 15-sig-digit
rule. I have not been successful. The simplest observation might be: in
my second counter-example and in your counter-example with 1E+6, the
mantissa of the actual result is zero. That is, the difference is an exact
power of two. But the same can be said of my first counter-example.
The second-simplest speculation is that the operands of the last subtraction
differ only in some number of the least significant bits of the binary
representation. That has not panned out either. I've toyed with a number
of more complex bit-twiddling, to no avail. I'm giving up.
Much ado about nothing. Although reverse-engineering is always fun, it
should not matter if people do the right thing anyway, using ROUND
prolifically or setting "Precision as displayed".
----- original message -----
"Jerry W. Lewis" wrote in message
...
The characterization 'if the result of the last operation is "close" to
zero'
may be misleading. Consider the following experiment:
In A1, place the formula =1+2^-50
In A3, place the formula =A1*A2-A2
In A4, place the formula =(A1*A2-A2)
In A5 place the formula =A1*A2&""
In A6 place the formula =A2&""
A4 will be nonzero, as it should be
If A2 contains any of 0.0000001, 1, 1E+25, 1E+100, 1E+300, 1E+307, then A3
will be zero even though the difference ranges from 9E-23 to 9E+291. Most
of
these results are hardly "small". The issue seems to be that the two
numbers
involved in the final subtraction are identical to 15 significant figures.
Even this characterization is not perfect, since 1000000 in A2 will give a
non-zero A3 even though A5 and A6 are still identical.
Jerry
"JoeU2004" wrote:
...
The following is my best explanation, deduced from experiments:
If the last operation is addition or subtraction that results in a change
from the intermediate result of the previous operations, and if the
result
of the last operation is "close" to zero, Excel will return exactly zero.
Otherwise, Excel will return the exact result of the operations. Note
that
a closing parenthesis (")") is considered an operation in this context.
(Sigh.)
Even that explanation is flawed insofar as it does not define what
"close"
is. That's MS's fault.
...