Why PasteSpecialValue doesnt always dupe binary value exactly?
 

Usually when I copy-and-paste-special-value a numeric value,
the target cell gets exactly the same binary value that the
original cell had.

But I stumbled upon one case (I'm sure there are others) where
that is not true. That is, the pasted value has a different binary
representation.

Under what conditions does this happen?

Consider the following two examples. (The binary value is on
the right. I use Excel 2003.)

1. Unusual(?) case.

A1: 2.00666666666667
A2: =100*round(A1,2) // &h40691FFF,FFFFFFFF
A3: 201 // &h40692000,00000000
A4: copy-and-PSV from A2 // &h40692000,00000000

A2 displays the same character repesentation as A3, even in
Scientific format with 14 DP. But the binary values differ
by 2^-45. As shown, the pasted binary value of A4 is the
same as A3, not A2.

2. Usual(?) case.

B1: 0.999999999999972 // &h3FEFFFFF,FFFFFF04
B2: =B1 - 2^-53 // &h3FEFFFFF,FFFFFF03
B3: copy-and-PSV from B2 // &h3FEFFFFF,FFFFFF03

Again, B1 and B2 display the same character representation
in Scientific with 14 DP. But the binary values differ by
2^-53. Contrary to Example 1, the binary value of B3 is the
same as B2, not B1.

I say "contrary" because to explain Example 1, I hypothesized
that when a value is copied to the Clipboard, Excel converts
the binary value to its most precise character representation,
then converts from the character representation to binary when
we PSV. Example 2 seems to contradict that theory.

Every other theory I've hypothesized does not seem to explain
when PSV will and will not change the binary value.

Example 2 differs by a much smaller amount than Example 1.
The difference in Example 2 is even a slightly smaller percentage
(about 1.11E-16) of the binary value associated with the character
representation than the difference in Example 1 (about 1.414E-E16).

Both examples differ in the least-significant bit. And the LSB
differs in the same "direction", namely from odd to even, which
may be significant when considering the (default) rules for
rounding
from 80-bit to 64-bit floating-point in the Intel FPU.

Why PasteSpecialValue doesnt always dupe binary value exactly?
 

On Aug 1, 10:00 pm, joeu2004 wrote:
I say "contrary" because to explain Example 1, I hypothesized
that when a value is copied to the Clipboard, Excel converts
the binary value to its most precise character representation,
then converts from the character representation to binary when
we PSV. Example 2 seems to contradict that theory.

Well, I do suspect that this has something to do with conversion into
and from the character representation.

PSV changes all the binary values that convert to 2.01000000000000E+02
to the binary value for 201. Similarly for some other "integral"
values (when converted to 15 significant digits) that I tried. But
all the binary values that convert to 2.00999999999999E+02 and
2.01000000000001E+02 are unchanged by PSV.


----- original posting -----

On Aug 1, 10:00*pm, joeu2004 wrote:
Usually when I copy-and-paste-special-value a numeric value,
the target cell gets exactly the same binary value that the
original cell had.

But I stumbled upon one case (I'm sure there are others) where
that is not true. *That is, the pasted value has a different binary
representation.

Under what conditions does this happen?

Consider the following two examples. *(The binary value is on
the right. *I use Excel 2003.)

1. Unusual(?) case.

* *A1: *2.00666666666667
* *A2: *=100*round(A1,2) * * * * *// &h40691FFF,FFFFFFFF
* *A3: *201 * * * * * * * * * * * * * * *// &h40692000,00000000
* *A4: *copy-and-PSV from A2 *// &h40692000,00000000

* *A2 displays the same character repesentation as A3, even in
* *Scientific format with 14 DP. *But the binary values differ
* *by 2^-45. *As shown, the pasted binary value of A4 is the
* *same as A3, not A2.

2. Usual(?) case.

* *B1: *0.999999999999972 * * *// &h3FEFFFFF,FFFFFF04
* *B2: *=B1 - 2^-53 * * * * * * * * * // &h3FEFFFFF,FFFFFF03
* *B3: *copy-and-PSV from B2 *// &h3FEFFFFF,FFFFFF03

* *Again, B1 and B2 display the same character representation
* *in Scientific with 14 DP. *But the binary values differ by
* *2^-53. *Contrary to Example 1, the binary value of B3 is the
* *same as B2, not B1.

* *I say "contrary" because to explain Example 1, I hypothesized
* *that when a value is copied to the Clipboard, Excel converts
* *the binary value to its most precise character representation,
* *then converts from the character representation to binary when
* *we PSV. *Example 2 seems to contradict that theory.

* *Every other theory I've hypothesized does not seem to explain
* *when PSV will and will not change the binary value.

* *Example 2 differs by a much smaller amount than Example 1.
* *The difference in Example 2 is even a slightly smaller percentage
* *(about 1.11E-16) of the binary value associated with the character
* *representation than the difference in Example 1 (about 1.414E-E16)..

* *Both examples differ in the least-significant bit. *And the LSB
* *differs in the same "direction", namely from odd to even, which
* *may be significant when considering the (default) rules for
rounding *from 80-bit to 64-bit floating-point in the Intel FPU.

Why PasteSpecialValue doesnt always dupe binary value exactly?
 

This issue is more common than you might imagine. Prior to 2007, there were
millions of floating point numbers that simply could not exist as a constant
value in Excel. You could generate them as a formula result, but if you
pasted that result as a value, then you lost some of the trailing bits.

Excel 2007 allows any valid floating point number to exist as a constant,
but that means that the display routines had never been tested for millions
of values, some of which did not initially display properly -- values just
below 2^16-1 displayed as 100000 and values just below 2^16 displayed as
100001, e.g.
http://groups.google.com/group/micro...31dabc74c22d38

Jerry

"joeu2004" wrote:

Usually when I copy-and-paste-special-value a numeric value,
the target cell gets exactly the same binary value that the
original cell had.

But I stumbled upon one case (I'm sure there are others) where
that is not true. That is, the pasted value has a different binary
representation.

Under what conditions does this happen?

Consider the following two examples. (The binary value is on
the right. I use Excel 2003.)

1. Unusual(?) case.

A1: 2.00666666666667
A2: =100*round(A1,2) // &h40691FFF,FFFFFFFF
A3: 201 // &h40692000,00000000
A4: copy-and-PSV from A2 // &h40692000,00000000

A2 displays the same character repesentation as A3, even in
Scientific format with 14 DP. But the binary values differ
by 2^-45. As shown, the pasted binary value of A4 is the
same as A3, not A2.

2. Usual(?) case.

B1: 0.999999999999972 // &h3FEFFFFF,FFFFFF04
B2: =B1 - 2^-53 // &h3FEFFFFF,FFFFFF03
B3: copy-and-PSV from B2 // &h3FEFFFFF,FFFFFF03

Again, B1 and B2 display the same character representation
in Scientific with 14 DP. But the binary values differ by
2^-53. Contrary to Example 1, the binary value of B3 is the
same as B2, not B1.

I say "contrary" because to explain Example 1, I hypothesized
that when a value is copied to the Clipboard, Excel converts
the binary value to its most precise character representation,
then converts from the character representation to binary when
we PSV. Example 2 seems to contradict that theory.

Every other theory I've hypothesized does not seem to explain
when PSV will and will not change the binary value.

Example 2 differs by a much smaller amount than Example 1.
The difference in Example 2 is even a slightly smaller percentage
(about 1.11E-16) of the binary value associated with the character
representation than the difference in Example 1 (about 1.414E-E16).

Both examples differ in the least-significant bit. And the LSB
differs in the same "direction", namely from odd to even, which
may be significant when considering the (default) rules for
rounding
from 80-bit to 64-bit floating-point in the Intel FPU.