Sorry for misreading the message late last night!
Now in D1 I have =D2D(C1) and I get 0.280000000000000026645352591

I except you have a newer D2D which displays more digits.
Now I will butt out

Jerry: what are your views on the new Excel 2010 stats functions?
Bernard


"Jerry W. Lewis" wrote in message
...
Sorry, Bernard, I didn't consider that a more general audence might still
be
following such an extremely specialized thread. You can use my D2D VBA
function from
http://groups.google.com/group/micro...fb95785d1eaff5
to see the decimal equivalent of the internal binary representation.

Cheers,
Jerry

"Bernard Liengme" wrote:

Jerry,
I have your formula in A1 and it returns 0.28
When I Copy/ Paste Special | Values to C1 I get 0.28
No amount of tapping the Increase decimal will give anything but
0.2800000000.....
Am I missing something?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Jerry W. Lewis" wrote in message
...
Sorry for the long delayed response. When I briefly scanned this
thread a
while back, I assumed that it had been put to bed, and so didn't read
it
carefully and didn't give it any thought. Tonight, I accidentally
typed
September instead of October into my Google message filter, and ended
up
reading it a bit more carefully.

My empirical observation is that prior to Excel 2007, there were
certain
values that for unexplained reasons were not permitted as an Excel
constant,
even though they were permitted as the result of calculations. What
typically happens, is what you have observed here, all the binary
variants
for the 15 digit display collapse into a single binary value for each
of
the
'unpermitted' constants.

Consider the following VBA function to convert from a string to a
floating
point
Function D2F(x)
D2F = CDbl(x)
End Function
In a worksheet cell,
=D2F("0.279999999999999527044991509683313779532909 393310546875")
will give a floating point value in Excel that has exactly the intended
value. If you Copy and Paste Special|Values from this cell into
another,
you
will find that the destination cell contains
0.280000000000000026645352591003756970167160034179 6875
Thus, the issue is not with your VBA code, but is due to the fact that
Excel
simply does not permit these binary values as constants in cells.

Excel 2007 seemed to remove this restriction

http://groups.google.com/group/micro...31dabc74c22d38
but it appears that you (like me) do not use Excel 2007.

Jerry

"JoeU2004" wrote:

I know this is "stoopid" because I'm sure I have done this before with
no
problem.

But for some reason, today -- with the stars, sun, moon and earth
aligned
such as they are -- the following macro is not doing what I expect.

What am I doing wrong?


Sub doit()
Dim x As Double, s As String, i As Long
For i = -9 To 8
x = 0.28 + i * 2 ^ -54
Debug.Print dbl2dec(x)
Cells(1 + i + 9, 2) = x
Next i
End Sub


dbl2dec() is my function that formats binary floating point exactly.
The
debug.print output is what I expect, namely:

i=-9: 0.279999999999999,52704499150968331377953290939331 0546875
i=-8: 0.279999999999999,58255614274094114080071449279785 15625
i=-7: 0.279999999999999,63806729397219896782189607620239 2578125
i=-6: 0.279999999999999,69357844520345679484307765960693 359375
i=-5: 0.279999999999999,74908959643471462186425924301147 4609375
i=-4: 0.279999999999999,80460074766597244888544082641601 5625
i=-3: 0.279999999999999,86011189889723027590662240982055 6640625
i=-2: 0.279999999999999,91562305012848810292780399322509 765625
i=-1: 0.279999999999999,97113420135974592994898557662963 8671875
i=0: 0.280000000000000,02664535259100375697016716003417 96875
i=1: 0.280000000000000,08215650382226158399134874343872 0703125
i=2: 0.280000000000000,13766765505351941101253032684326 171875
i=3: 0.280000000000000,19317880628477723803371191024780 2734375
i=4: 0.280000000000000,24868995751603506505489349365234 375
i=5: 0.280000000000000,30420110874729289207607507705688 4765625
i=6: 0.280000000000000,35971225997855071909725666046142 578125
i=7: 0.280000000000000,41522341120980854611843824386596 6796875
i=8: 0.280000000000000,47073456244106637313961982727050 78125

So "x" is indeed getting the values that I expect.

But the results in B1:B18 are all the same binary representation of
the
constant 0.28 (see i=0 above), not the 18 different binary
representations
that I generate in the macro.

(C1 is the formula =dbl2dec(B1), which is copied down through C18.)

As a double-check, I do get the 18 different binary representations if
A1:A18 are the values -9 through 8, and I put the following formula
into
B1
and copy down through B18:

=0.28 + A1 * 2^-54

I think that rules out any user errors w.r.t calculation modes (manual
v.
auto; "precision as displayed"; etc).

I know that I can work around the problem by changing the cell
assignment
in
the macro to:

Cells(1 + i + 9, 2).Formula = "=0.28 + (" & i & ")* 2 ^ -54"

But why can't I return the exact binary representation in
cells(...).value
directly?

As I said, I'm certain I have done this before. So I must be having a
"senior moment" and doing something "stoopid".

Please thump me on the head and tell me what it is.

-----

Aha!

I wrote:
As I said, I'm certain I have done this before. So
I must be having a "senior moment" and doing something
"stoopid".

Actually, what I have probably done before is:

Function doit2(i As Long) As Double
Dim x As Double, s As String
x = 0.28 + i * 2 ^ -54
Debug.Print "i=" & i & ": " & dbl2dec(x)
doit2 = x
End Function

where I have =doit3(A1) in B1 and copy down through B18, and A1:A18
are
the
values -9 through 8.

And __that__ does work as expected.

So perhaps my question is: why doesn't this work using a macro?

And more to the point: how do I make it work using a macro, other
than
storing a formula into the cell?

-----

WAG: When I store into .value in a macro, I wonder if Excel is
converting
the double to a numeric string, subject to its display limit of 15
significant digits, then treating it as data entry and converting it
back
to
a number. Say what?!