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.

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.

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.