Re: Simple subtraction formula returning strange results = Excel g
- From: "JoeU2004" <joeu2004@xxxxxxxxxxx>
- Date: Wed, 1 Apr 2009 11:12:13 -0700
Previously, I wrote:
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.
Aha! Is it really that simple?
Excel replaces the exact result with zero when the difference between the operands of the last subtraction [1], each taken as a 64-bit integer, is of the form +/- n*2^x, where n is 1 to 7 and 2^x represents the least-significant bit of the mantissa.
(No wonder the MS KB writer could not describe this correctly. I don't think I can explain it any differently myself.)
Consider each of the examples we have discussed in this thread, starting with Jerry's. The 64-bit floating-point representation is shown in the stylized hex form &hEEEMMMMM,M...M, where "E" is the biased exponent and "M" is the mantissa.
1. A1: =1+2^-50
A2: 1E-7 &h3E7AD7F2,9ABCAF48
A3: =A5-A2
A4: =(A5-A2)
A5: =A1*A2 &h3E7AD7F2,9ABCAF4F
Note that A5 could be written as: =A2+7*2^-76. Replacing 7 with 1 to 6 always results in a zero in A3. But A2+8*2^76 produces the same non-zero result in A3 and A4. In all cases, A5 displays as 1.00000000000000E-07.
FYI, I do not see a change in the displayed value in A5 until A2+39*2^-76. So I think the limit of 1 to 7 for "n" is arbitrary or someone's idea of a compromise. (See example #7 below.)
2. Same as #1, but:
A2: 1E25 &h45208B2A,2C280291
A5: =A1*A2 &h45208B2A,2C280295
A5 is: =A2+4*2^31. Replacing 4 with 1 to 7 always results in a zero in A3. But 8 produces the same non-zero result in A3 and A4. In all cases, A5 displays as 1.00000000000000E+25.
3. Same as #1, but:
A2: 1E100 &h54B249AD,2594C37D
A5: =A1*A2 &h54B249AD,2594C382
A5 is: =A2+5*2^280. Replacing 5 with 1 to 7 always results in a zero in A3. But 8 produces the same non-zero result in A3 and A4. In all cases, A5 displays as 1.00000000000000E+100.
4. Same as #1, but:
A2: 1E300 &h7E37E43C,8800759C
A5: =A1*A2 &h7E37E43C,880075A2
A5 is: =A2+6*2^944. Replacing 6 with 1 to 7 always results in a zero in A3. But 8 produces the same non-zero result in A3 and A4. In all cases, A5 displays as 1.00000000000000E+300.
5. Same as #1, but:
A2: 1E307 &h7FAC7B1F,3CAC7433
A5: =A1*A2 &h7FAC7B1F,3CAC743A
A5 is: =A2+7*2^967. Replacing 7 with 1 to 6 always results in a zero in A3. But 8 produces the same non-zero result in A3 and A4. In all cases, A5 displays as 1.00000000000000E+307.
6. Same as #1, but:
A2: 1E6 &h412E8480,00000000
A5: =A1*A2 &h412E8480,00000008
A5 is: =A2+8*2^-33. Because "n" is 8, A3 and A4 have the same non-zero result. Replacing 8 with 1 to 7 always results in a zero in A3. In all cases, A5 displays as 1.00000000000000E+06.
7. B2: =2^1023+(2^1023-2^971) &h7FEFFFFF,FFFFFFFF
B3: =2^1023+(2^1023-5*2^971) &h7FEFFFFF,FFFFFFFB
B4: =B3-B2
B5: =(B3-B2)
B3 is: =B2-4*2^971. Replacing 4 to 1 to 7 always results in a zero in B4. But 8 produces the same non-zero result in B4 and B5. When "n" is 1 to 3, B3 displays as 1.79769313486232E+308, the same as B2. When "n" is 4 to 8, B3 displays as 1.79769313486231E+308.
FYI, B2-8*2^971 is 2^1023+(2^1023-9*2^971), my second example in my previous posting.
8. Same as #7 but (one of Ginger's first example):
B2: 14369.10 &h40CC108C,CCCCCCCD
B3: =(39795-13530.90-11895) &h40CC108C,CCCCCCCC
B2 is =B3+1*2^-39, and B4 is zero. Replacing 1 with 2 to 7 always results in zero in B4. But 8 produces the same non-zero result in B4 and B5. In all cases, B2 displays as 1.43691000000000E+04, the same as B3.
When Ginger effectively wrote 39795 - 13530.90 - 11895 - 14369.10 - 0 by including cells with zero, the two operands of the last subtraction are &h40CC108C,CCCCCCCD and 0. Obviously, "n" is not 1 to 7, so B4 and B5 are the same non-zero result.
Similarly with Ginger's second set of examples, namely 0.5 - 0.4 - 0.1 v. 0.5 - 0.4 - 0.1 - 0.
I am fairly confident of my conclusion. But when I get a chance, I might test with randomly generated "close" operands.
-----
Endnotes:
[1] MS says "addition or subtraction". For the former, I presume they mean the addition of operands of opposite signs, which is just another form of subtraction.
------ original message -----
"JoeU2004" <joeu2004@xxxxxxxxxxx> wrote in message news:ugT9tYrsJHA.4068@xxxxxxxxxxxxxxxxxxxxxxx
"Jerry W. Lewis" <post_a_reply@xxxxxxxxxxxxx> 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" <post_a_reply@xxxxxxxxxxxxx> wrote in message news:0C19C96E-62B0-4665-854D-5909B1F10F15@xxxxxxxxxxxxxxxxThe 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.
.
- References:
- Prev by Date: RE: Weighted Regression
- Next by Date: DROP DOWN LIST
- Previous by thread: Re: Simple subtraction formula returning strange results = Excel g
- Next by thread: Averaging Blank Cells
- Index(es):
Relevant Pages
|
