J
joeu2004
Sigh, I had forgotten about this old thread and did not see Jerry's
posting before today. I stumbled across it when I rediscovered the
problem recently and searched for any relevant threads. Imagine my
surprise! (A "senior moment".) But Google Groups does not permit me
to simply post a reply to the original thread :-(.
Jerry seems to be suggesting that Excel converts the value to a
displayable constant (first 15 significant digits), then copies or
pastes that constant.
That was my first hypothesis, too, as noted in my original posting.
And that rule does seem to apply when pasting into other applications
such as Notepad and MSWord.
But as I noted in my original posting, I found a counter-example that
disproves that theory for copy-and-paste-special-value (CAPSV) in
Excel.
Specifically, I showed that 0.999999999999972 - 2^-53 CAPSVs exactly.
That is, the internal representation of the original and pasted value
is &h3FEFFFFF,FFFFFF03 [1]. The cell does display 0.999999999999972
when formatted to 15 sig digits. But its internal representation is
different from the internal representation of 0.999999999999972, which
is &h3FEFFFFF,FFFFFF04 [3]. The difference is exactly 2^-53, the
smallest one-bit incremental difference [2].
And FYI, this behavior (sometimes, but not always, altering the
internal representation) also happens when storing into the Value and
Value2 properties of Excel range objects in V BA. For example:
Dim x As Double, y as Double
y = 201
x = 100 * 2.01
Debug.Print "y: "; dbl2bin(y); " "; dbl2dec(y)
Debug.Print "x: "; dbl2bin(x); " "; dbl2dec(x)
Range("a1") = x
Debug.Print "A1: "; dbl2bin(Range("a1")); " "; dbl2dec(Range
("a1"))
DBL2BIN and DBL2DEC are my own UDFs that show the exact internal
representation in binary and decimal. The Debug.Print statements show
that A1 is "y", not "x"; to wit:
y: &h40692000,00000000 201
x: &h40691FFF,FFFFFFFF
200.999999999999,971578290569595992565155029296875
A1: &h40692000,00000000 201
I conclude that this behavior is driven by an Excel heuristic that
applies to the PasteSpecial xlPasteValues method and the Value and
Value2 properties -- and perhaps other similar operations that store
into Excel objects.
But that heuristic does not apply to UDF results. For example, the
following UDF returns exactly "x":
Function capsv()
Dim x As Double, y as Double
y = 201
x = 100 * 2.01
Debug.Print "y: "; dbl2bin(y); " "; dbl2dec(y)
Debug.Print "x: "; dbl2bin(x); " "; dbl2dec(x)
capsv = x
End Function
Based on many examples, my best explanation of the heuristic is: if
the original value rounded to its 15-sig-digit representation (the
"modified value") is represented exactly within 15 sig digits, convert
the original value to the modified value.
I think that explanation is "close". But, sigh, I found counter-
examples that contradict it.
First, to demonstrate the heuristic, consider the following, example
#1 (based on a random number):
y = 0.200653076171871
x = y + 2 ^ -55
y: &h3FC9AEFF,FFFFFF70
0.200653076171871,003197111349436454474925994873046875
x: &h3FC9AEFF,FFFFFF71
0.200653076171871,0309526869650653679855167865753173828125
A1: &h3FC9AEFF,FFFFFF71
0.200653076171871,0309526869650653679855167865753173828125
Note that A1 is "x", not "y". That fits the rule because
0.200653076171871 cannot be represented exactly within 15 sig digits,
as shown.
Second, consider example #2:
y = 0.200653076171875
x = y + 2 ^ -55
y: &h3FC9AF00,00000000 0.200653076171875
x: &h3FC9AF00,00000001
0.200653076171875,0277555756156289135105907917022705078125
A1: &h3FC9AF00,00000000 0.200653076171875
A1 is "y", not "x". Again, that fits the rule because
0.200653076171875 is represented exactly within 15 sig digits.
My explanation of the heuristic seems to work. But now consider
example #3:
y = 200.653076171875
x = y + 2 ^ -45
y: &h406914E6,00000000 200.653076171875
x: &h406914E6,00000001
200.653076171875,028421709430404007434844970703125
A1: &h406914E6,00000001
200.653076171875,028421709430404007434844970703125
A1 is "x". I expected "y" because 200.653076171875 is represented
exactly within 15 sig digits.
Arguably, perhaps the heuristic applies only when the magnitude of the
difference between original and modified values is sufficiently small
-- something less than 2^-45.
But consider counter-example #4:
y = 200.640625
x = y + 2 ^ -45
y: &h40691480,00000000 200.640625
x: &h40691480,00000001
200.640625000000,028421709430404007434844970703125
A1: &h40691480,00000000 200.640625
A1 is "y", following the heuristic, despite the fact that the
magnitude of the difference is the same as example #3, and the
constant "y" in example #3 and #4 are similar in magnitude. (In fact,
the example #4 "y" was derived from example #3.)
So once again, I am out of theories.
Based on other examples, I suspect the heuristic might behave
differently under certain conditions, making it that much harder to
intuit.
But if anyone can demonstrate a viable heuristic, I'm all ears.
(Well, eyes. ;->)
Endnotes:
[1] The form &h3FEFFFFF,FFFFFF03 is my stylized representation of the
64-bit floating-point value in "little endian" order (aka "network
order"), which is not the storage order in Pentium-compatible
systems. The 12-bit sign and biased exponent are in the first 3 hex
characters ("3FE"), and the 52-bit mantissa follows ("F...F03"). The
comma separates upper and lower 32 bits.
[2] The operative phrase is "one-bit" incremental difference. For
example, the two-bit increment 2^-54 + 2^-55 is smaller than 2^-53,
but both =0.5 + 2^-53 and =0.5 + (2^-54 + 2^-55) result in the same
internal representation. That is, both differ from 0.5 by 2^-53,
thanks to the 80-bit FP representation and default FP rounding
algorithm implemented in Pentium-compatible FPUs.
[3] 0.999999999999972 - 2^-53 is stored exactly as
0.999999999999971,91135747698353952728211879730224609375. (The comma
demarcates 15 significant digits to the left.) It displays as
0.999999999999972; but 0.999999999999972 is stored exactly as
0.999999999999972,022379779446055181324481964111328125.
----- original posting -----
posting before today. I stumbled across it when I rediscovered the
problem recently and searched for any relevant threads. Imagine my
surprise! (A "senior moment".) But Google Groups does not permit me
to simply post a reply to the original thread :-(.
Jerry seems to be suggesting that Excel converts the value to a
displayable constant (first 15 significant digits), then copies or
pastes that constant.
That was my first hypothesis, too, as noted in my original posting.
And that rule does seem to apply when pasting into other applications
such as Notepad and MSWord.
But as I noted in my original posting, I found a counter-example that
disproves that theory for copy-and-paste-special-value (CAPSV) in
Excel.
Specifically, I showed that 0.999999999999972 - 2^-53 CAPSVs exactly.
That is, the internal representation of the original and pasted value
is &h3FEFFFFF,FFFFFF03 [1]. The cell does display 0.999999999999972
when formatted to 15 sig digits. But its internal representation is
different from the internal representation of 0.999999999999972, which
is &h3FEFFFFF,FFFFFF04 [3]. The difference is exactly 2^-53, the
smallest one-bit incremental difference [2].
And FYI, this behavior (sometimes, but not always, altering the
internal representation) also happens when storing into the Value and
Value2 properties of Excel range objects in V BA. For example:
Dim x As Double, y as Double
y = 201
x = 100 * 2.01
Debug.Print "y: "; dbl2bin(y); " "; dbl2dec(y)
Debug.Print "x: "; dbl2bin(x); " "; dbl2dec(x)
Range("a1") = x
Debug.Print "A1: "; dbl2bin(Range("a1")); " "; dbl2dec(Range
("a1"))
DBL2BIN and DBL2DEC are my own UDFs that show the exact internal
representation in binary and decimal. The Debug.Print statements show
that A1 is "y", not "x"; to wit:
y: &h40692000,00000000 201
x: &h40691FFF,FFFFFFFF
200.999999999999,971578290569595992565155029296875
A1: &h40692000,00000000 201
I conclude that this behavior is driven by an Excel heuristic that
applies to the PasteSpecial xlPasteValues method and the Value and
Value2 properties -- and perhaps other similar operations that store
into Excel objects.
But that heuristic does not apply to UDF results. For example, the
following UDF returns exactly "x":
Function capsv()
Dim x As Double, y as Double
y = 201
x = 100 * 2.01
Debug.Print "y: "; dbl2bin(y); " "; dbl2dec(y)
Debug.Print "x: "; dbl2bin(x); " "; dbl2dec(x)
capsv = x
End Function
Based on many examples, my best explanation of the heuristic is: if
the original value rounded to its 15-sig-digit representation (the
"modified value") is represented exactly within 15 sig digits, convert
the original value to the modified value.
I think that explanation is "close". But, sigh, I found counter-
examples that contradict it.
First, to demonstrate the heuristic, consider the following, example
#1 (based on a random number):
y = 0.200653076171871
x = y + 2 ^ -55
y: &h3FC9AEFF,FFFFFF70
0.200653076171871,003197111349436454474925994873046875
x: &h3FC9AEFF,FFFFFF71
0.200653076171871,0309526869650653679855167865753173828125
A1: &h3FC9AEFF,FFFFFF71
0.200653076171871,0309526869650653679855167865753173828125
Note that A1 is "x", not "y". That fits the rule because
0.200653076171871 cannot be represented exactly within 15 sig digits,
as shown.
Second, consider example #2:
y = 0.200653076171875
x = y + 2 ^ -55
y: &h3FC9AF00,00000000 0.200653076171875
x: &h3FC9AF00,00000001
0.200653076171875,0277555756156289135105907917022705078125
A1: &h3FC9AF00,00000000 0.200653076171875
A1 is "y", not "x". Again, that fits the rule because
0.200653076171875 is represented exactly within 15 sig digits.
My explanation of the heuristic seems to work. But now consider
example #3:
y = 200.653076171875
x = y + 2 ^ -45
y: &h406914E6,00000000 200.653076171875
x: &h406914E6,00000001
200.653076171875,028421709430404007434844970703125
A1: &h406914E6,00000001
200.653076171875,028421709430404007434844970703125
A1 is "x". I expected "y" because 200.653076171875 is represented
exactly within 15 sig digits.
Arguably, perhaps the heuristic applies only when the magnitude of the
difference between original and modified values is sufficiently small
-- something less than 2^-45.
But consider counter-example #4:
y = 200.640625
x = y + 2 ^ -45
y: &h40691480,00000000 200.640625
x: &h40691480,00000001
200.640625000000,028421709430404007434844970703125
A1: &h40691480,00000000 200.640625
A1 is "y", following the heuristic, despite the fact that the
magnitude of the difference is the same as example #3, and the
constant "y" in example #3 and #4 are similar in magnitude. (In fact,
the example #4 "y" was derived from example #3.)
So once again, I am out of theories.
Based on other examples, I suspect the heuristic might behave
differently under certain conditions, making it that much harder to
intuit.
But if anyone can demonstrate a viable heuristic, I'm all ears.
(Well, eyes. ;->)
Endnotes:
[1] The form &h3FEFFFFF,FFFFFF03 is my stylized representation of the
64-bit floating-point value in "little endian" order (aka "network
order"), which is not the storage order in Pentium-compatible
systems. The 12-bit sign and biased exponent are in the first 3 hex
characters ("3FE"), and the 52-bit mantissa follows ("F...F03"). The
comma separates upper and lower 32 bits.
[2] The operative phrase is "one-bit" incremental difference. For
example, the two-bit increment 2^-54 + 2^-55 is smaller than 2^-53,
but both =0.5 + 2^-53 and =0.5 + (2^-54 + 2^-55) result in the same
internal representation. That is, both differ from 0.5 by 2^-53,
thanks to the 80-bit FP representation and default FP rounding
algorithm implemented in Pentium-compatible FPUs.
[3] 0.999999999999972 - 2^-53 is stored exactly as
0.999999999999971,91135747698353952728211879730224609375. (The comma
demarcates 15 significant digits to the left.) It displays as
0.999999999999972; but 0.999999999999972 is stored exactly as
0.999999999999972,022379779446055181324481964111328125.
----- original posting -----