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.