Why PasteSpecialValue doesnt always dupe binary value exactly?

[joeu2004]

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.

 

[joeu2004]

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 -----

 

[Jerry W. Lewis]

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/microsoft.public.excel.crashesgpfs/msg/3b31dabc74c22d38

Jerry