How to strore exact double from macro into Excel cell?

[JoeU2004]

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,527044991509683313779532909393310546875
i=-8: 0.279999999999999,5825561427409411408007144927978515625
i=-7: 0.279999999999999,638067293972198967821896076202392578125
i=-6: 0.279999999999999,69357844520345679484307765960693359375
i=-5: 0.279999999999999,749089596434714621864259243011474609375
i=-4: 0.279999999999999,804600747665972448885440826416015625
i=-3: 0.279999999999999,860111898897230275906622409820556640625
i=-2: 0.279999999999999,91562305012848810292780399322509765625
i=-1: 0.279999999999999,971134201359745929948985576629638671875
i=0: 0.280000000000000,0266453525910037569701671600341796875
i=1: 0.280000000000000,082156503822261583991348743438720703125
i=2: 0.280000000000000,13766765505351941101253032684326171875
i=3: 0.280000000000000,193178806284777238033711910247802734375
i=4: 0.280000000000000,24868995751603506505489349365234375
i=5: 0.280000000000000,304201108747292892076075077056884765625
i=6: 0.280000000000000,35971225997855071909725666046142578125
i=7: 0.280000000000000,415223411209808546118438243865966796875
i=8: 0.280000000000000,4707345624410663731396198272705078125

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!

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

 

[Dave Peterson]

Excel keeps track of 15 significant digits when you treat the entry as a number.

VBA keeps track of lots(?) more if you use cDec() (28 I think).

Maybe you could use cdec() or just try returning text.

with Cells(1 + i + 9, 2)
.numberformat = "@" 'text
.value = x
'or maybe
'.value = cstr(x)
end with

Untested, though.

 

[Gary''s Student]

What are your UDFs ??

 

[JoeU2004]

VBA keeps track of lots(?) more if you use cDec() (28 I think).
Maybe you could use cdec() or just try returning text.

I think you missed the point. Let me rephrase....

Suppose we have the following VBA procedures:

Function doit1()
doit1 = Range("a2")
End Function

Sub doit2()
Range("a4") = Range("a2")
End Sub

Enter the following in Excel:

A1: 0.28
A2: =0.28 + 2^-54
A3: =doit1()
A4: (to be filled in by macro doit2)

Execute the doit2() macro, then enter the following in Excel:

B1: =A1-A2=0 (FALSE)
B2: =A3-A4=0 (FALSE!)
B3: =A2-A3=0 (TRUE)
B4: =A1-A4=0 (TRUE!)

The FALSE value of B1 demonstrates that the internal binary representation
of A1 and A2 are different as intended, even though we cannot see that when
formatting to 15 decimal places, Excel's conversion limit.

The FALSE value of B2 demonstrates that function doit1 returns a different
internal binary representation than macro doit2 returns.

Why is that? They both get their value from A2.

The TRUE value of B3 demonstrates that function doit1 faithfully duplicates
the internal binary representation in A2.

The TRUE value of B4 demonstrates that macro doit2 converts A2 to the
internal binary representation of the constant 0.28 (A1).

Can we coerce macro doit2 to faithfully duplicate the internal binary
representation in A2, as function doit1 does?

(Without employing some object property/method trick. I really want to use
expressions on the right-hand side of the assignment, and not rely on cell
references like Range("a2"). I am using the latter only for this example to
rule out discussions of differences between Excel and VBA, I hope.)

Excel keeps track of 15 significant digits when you treat the
entry as a number.

Ostensibly, this has nothing to do with Excel's 15-significant-digit
conversion limit.

Unless.... Are you confirming my WAG, to wit: in a macro, but not in a
function, Excel (or VBA?!) first converts the binary value of an assignment
to a cell to a numeric string, subject to its 15-significant-digit
conversion limit; then Excel converts the numeric string to binary as if it
were entered manually.

(Of course, we cannot have "an assignment to a cell" in a function. What I
mean by "not in a function" is: the same binary-to-string-to-binary
conversion does not happen for a function return value -- an assignment to
the function name.)

That is only speculation at the moment. If it is true, where should I have
learned about that difference between macros cell assignments and functions
return values?

Arguably, this probably matters to only two people in the whole world. (And
I'm not sure that Jerry even cares.) Most people try to avoid thinking
about the binary representation, and that's just fine.

PS: I am a software architect, so I relate to things better if I have an
understanding of the architectural relationships among the components of a
system. I really do know how Excel and VBA interact architecturally. Is
there someplace I can go to get that architectural knowledge? Is there a
"devil book" for Excel/VBA?


----- original message -----

 

[Dave Peterson]

You're right. I missed your point.

I thought you wanted something like:

Function myFunc() As Variant

Dim myVal As Variant
myVal = CDec(".12341234123412341234123412341234")
myFunc = myVal
'or
myFunc = "'" & myVal

End Function

I'm sure Mike Middleton will be able to offer a reasonable explanation (if you
can get his attention).

On the other hand, these 4 all returned True:

=A1=A2
=A3=A4
=A2=A3
=A1=A4

So maybe it's the subtraction that's (partially??) causing the trouble and the
comparison to 0 causing the other portion.

I'll eagerly <vbg> await Mike's response.

 

[JoeU2004]

On the other hand, these 4 all returned True:
=A1=A2
=A3=A4
=A2=A3
=A1=A4

That form is affected by Excel's attempt to hide "infinitesimal"
differences.

I wanted to avoid Excel's heuristic. That is why I wrote the comparison in
the form =A1-A2=0.

So maybe it's the subtraction that's (partially??) causing
the trouble and the comparison to 0 causing the other portion.

You continue to miss the point. There is no "trouble" with the comparisons.

The "trouble" is with the different behavior of assignment of a numeric
value to cell in a macro v. assignment of the same numeric value to a
function name (i.e. returning the value).

I am asking: why does the macro assignment change the binary value --
__that__ is the "trouble" -- whereas the function assignment preserves the
binary value -- that is the __desired__ behavior.

You seem to be overwhelmed by the details of the binary representation. I
suggest that you sit this one out.


----- original message -----

 

[JoeU2004]

The FALSE value of B2 demonstrates that function doit1 returns a different
internal binary representation than macro doit2 returns. Why is that?
They both get their value from A2.

On second thought, I suspect no one is likely to be able to answer the
question dispositively. In fact, it is probably something that no one was
aware of, since 99.9...9% of all Excel users see only the 15 significant
digits that Excel converts.

I really do [not] know how Excel and VBA interact architecturally.
Is there someplace I can go to get that architectural knowledge?

I suspect the explanation is the same as the reason why
copy-and-paste-special-value does not faithfully duplicate the internal
binary pattern, an observation I shared in these forums some time ago. I
remember that Jerry responded; but I don't remember if he had a dispositive
answer or simply a reasonable speculation.

Anyway, that's just the way it is.

As a software architect myself, I can easily imagine architectural
differences between macros and functions that might lend themselves to the
different behavior.

I do wish someone could answer the immediate question above, namely where I
could learn about these architectural differences.


----- original message -----

VBA keeps track of lots(?) more if you use cDec() (28 I think).
Maybe you could use cdec() or just try returning text.

I think you missed the point. Let me rephrase....

Suppose we have the following VBA procedures:

Function doit1()
doit1 = Range("a2")
End Function

Sub doit2()
Range("a4") = Range("a2")
End Sub

Enter the following in Excel:

A1: 0.28
A2: =0.28 + 2^-54
A3: =doit1()
A4: (to be filled in by macro doit2)

Execute the doit2() macro, then enter the following in Excel:

B1: =A1-A2=0 (FALSE)
B2: =A3-A4=0 (FALSE!)
B3: =A2-A3=0 (TRUE)
B4: =A1-A4=0 (TRUE!)

The FALSE value of B1 demonstrates that the internal binary representation
of A1 and A2 are different as intended, even though we cannot see that
when formatting to 15 decimal places, Excel's conversion limit.

The FALSE value of B2 demonstrates that function doit1 returns a different
internal binary representation than macro doit2 returns.

Why is that? They both get their value from A2.

The TRUE value of B3 demonstrates that function doit1 faithfully
duplicates the internal binary representation in A2.

The TRUE value of B4 demonstrates that macro doit2 converts A2 to the
internal binary representation of the constant 0.28 (A1).

Can we coerce macro doit2 to faithfully duplicate the internal binary
representation in A2, as function doit1 does?

(Without employing some object property/method trick. I really want to
use expressions on the right-hand side of the assignment, and not rely on
cell references like Range("a2"). I am using the latter only for this
example to rule out discussions of differences between Excel and VBA, I
hope.)

Excel keeps track of 15 significant digits when you treat the
entry as a number.

Ostensibly, this has nothing to do with Excel's 15-significant-digit
conversion limit.

Unless.... Are you confirming my WAG, to wit: in a macro, but not in a
function, Excel (or VBA?!) first converts the binary value of an
assignment to a cell to a numeric string, subject to its
15-significant-digit conversion limit; then Excel converts the numeric
string to binary as if it were entered manually.

(Of course, we cannot have "an assignment to a cell" in a function. What
I mean by "not in a function" is: the same binary-to-string-to-binary
conversion does not happen for a function return value -- an assignment to
the function name.)

That is only speculation at the moment. If it is true, where should I
have learned about that difference between macros cell assignments and
functions return values?

Arguably, this probably matters to only two people in the whole world.
(And I'm not sure that Jerry even cares.) Most people try to avoid
thinking about the binary representation, and that's just fine.

PS: I am a software architect, so I relate to things better if I have an
understanding of the architectural relationships among the components of a
system. I really do know how Excel and VBA interact architecturally. Is
there someplace I can go to get that architectural knowledge? Is there a
"devil book" for Excel/VBA?


----- original message -----

 

[Dave Peterson]

I didn't miss the point this time.

You want to know why excel does this. I don't think anyone will be able to
answer that question. They may be able to explain the behavior, but not the
why.

On the other hand, these 4 all returned True:
=A1=A2
=A3=A4
=A2=A3
=A1=A4

That form is affected by Excel's attempt to hide "infinitesimal"
differences.

I wanted to avoid Excel's heuristic. That is why I wrote the comparison in
the form =A1-A2=0.

So maybe it's the subtraction that's (partially??) causing
the trouble and the comparison to 0 causing the other portion.

You continue to miss the point. There is no "trouble" with the comparisons.

The "trouble" is with the different behavior of assignment of a numeric
value to cell in a macro v. assignment of the same numeric value to a
function name (i.e. returning the value).

I am asking: why does the macro assignment change the binary value --
__that__ is the "trouble" -- whereas the function assignment preserves the
binary value -- that is the __desired__ behavior.

You seem to be overwhelmed by the details of the binary representation. I
suggest that you sit this one out.

----- original message -----

 

[Dave Peterson]

ps.

that's what this meant:

I'll eagerly <vbg> await Mike's response.

You seem to be overwhelmed by the details of the binary representation. I
suggest that you sit this one out.

<<snipped>>--

Dave Peterson

 

[JoeU2004]

I didn't miss the point this time.

With all due respect, obviously you did.

You wrote: "So maybe it's the subtraction that's (partially??) causing the
trouble and the comparison to 0 causing the other portion".

So clearly, you think my question has something to do with the arithmetic
results.

My question has nothing to do with that. I am merely using the differences
in the arithmetic results -- the internal binary representation -- to
demonstrate the problem I am asking about.

You want to know why excel does this. I don't think anyone will be able
to
answer that question. They may be able to explain the behavior, but not
the
why.

I am not asking "why?" in the sense of "what is the motivation?". I used
the word "why?" in the sense of "what is going on?".

Yes, I am interested in the explanation of "the" behavior. What you do not
seem to understand is what "the" refers to.

I am asking about the difference in how VBA functions and macros communicate
results back to Excel. In the one case, exact binary results are
communicated; in the other case, a conversion takes place.

I do not mean to keep beating you up for your lack of understanding of the
question. But if I am not making myself clear to you, I am probably not
making myself clear to others, especially the person who can indeed answer
my question.

-----

Having said all that, I am satisfied with my own "explanation"
(rationalization).

As for "why?" (motivation), I usually do not try to answer that question.
But....

I suspect it is an accident of implementation.

But if there was a conscious thought given to this, I suspect the macro
behavior is an attempt to make the assignment of values to cells mimic data
entry, which is indeed limited to 15 significant digits.

(However, we are able to assign 1.79769313486231E+308 to Range("A1") in a
macro, whereas we are limited to 9.99999999999999E+307 for manual data entry
in Excel 2003. Details, details! ;->)

On the other hand, with functions, we are accustomed to getting exact binary
results. The 15-significant-digit limitation applies only to how Excel
displays the value, not to the internal binary representation.

For example, PI() returns the exactly
3.14159265358979,3115997963468544185161590576171875 (in A1). But that value
is displayed as 3.14159265358979 to 15 significant digits. If we enter
3.14159265358979 manually into A2, the exact value is
3.14159265358979,0007373494518105871975421905517578125. Consequently,
=A1-A2=0 returns FALSE.

(Yes, =A1=A2 returns TRUE. That is because Excel's heuristic for handling
"close to zero" values treats them as equal, but only in some contexts. For
a poor explanation, see http://support.microsoft.com/kb/78113/en-us.
Anyway, that has nothing to do with my question.)


So my question becomes, if I have the following:

A1: =PI()
A2: =doit1()
A3: (filled in by macro doit2)
B3: =A2-A3=0 (FALSE!)

where in doit1() we have

doit1 = Range("A1")

and in doit2() we have

Range("A3") = Range("A1")

why do (that is, explain the mechanism by which) A2 and A3 have different
values, which is demonstrated by the fact that B3 results in FALSE?


And to be clear, I want the macro behavior to mimic the function behavior.
That is, I want the macro to assign the exact internal binary representation
found in A1.

So no change to function doit1 is warranted.

I'll eagerly <vbg> await Mike's response.

As I noted in another posting in this thread, I suspect that Jerry attempted
to explain this anomaly (failure to replicate the exact internal binary
representation) some time ago when I posted a similar observation about
copy-and-paste-special-value.

Unfortunately, I cannot find that response. And I don't recall if Jerry's
response was dispositive or merely speculation.

Well, I suspect I beat that horse to death. I only hope this has clarified
the question, if not for you, then for someone else.


----- original message -----

 

[Dave Peterson]

Good luck.

I didn't miss the point this time.

With all due respect, obviously you did.

You wrote: "So maybe it's the subtraction that's (partially??) causing the
trouble and the comparison to 0 causing the other portion".

So clearly, you think my question has something to do with the arithmetic
results.

My question has nothing to do with that. I am merely using the differences
in the arithmetic results -- the internal binary representation -- to
demonstrate the problem I am asking about.

You want to know why excel does this. I don't think anyone will be able
to
answer that question. They may be able to explain the behavior, but not
the
why.

I am not asking "why?" in the sense of "what is the motivation?". I used
the word "why?" in the sense of "what is going on?".

Yes, I am interested in the explanation of "the" behavior. What you do not
seem to understand is what "the" refers to.

I am asking about the difference in how VBA functions and macros communicate
results back to Excel. In the one case, exact binary results are
communicated; in the other case, a conversion takes place.

I do not mean to keep beating you up for your lack of understanding of the
question. But if I am not making myself clear to you, I am probably not
making myself clear to others, especially the person who can indeed answer
my question.

-----

Having said all that, I am satisfied with my own "explanation"
(rationalization).

As for "why?" (motivation), I usually do not try to answer that question.
But....

I suspect it is an accident of implementation.

But if there was a conscious thought given to this, I suspect the macro
behavior is an attempt to make the assignment of values to cells mimic data
entry, which is indeed limited to 15 significant digits.

(However, we are able to assign 1.79769313486231E+308 to Range("A1") in a
macro, whereas we are limited to 9.99999999999999E+307 for manual data entry
in Excel 2003. Details, details! ;->)

On the other hand, with functions, we are accustomed to getting exact binary
results. The 15-significant-digit limitation applies only to how Excel
displays the value, not to the internal binary representation.

For example, PI() returns the exactly
3.14159265358979,3115997963468544185161590576171875 (in A1). But that value
is displayed as 3.14159265358979 to 15 significant digits. If we enter
3.14159265358979 manually into A2, the exact value is
3.14159265358979,0007373494518105871975421905517578125. Consequently,
=A1-A2=0 returns FALSE.

(Yes, =A1=A2 returns TRUE. That is because Excel's heuristic for handling
"close to zero" values treats them as equal, but only in some contexts. For
a poor explanation, see http://support.microsoft.com/kb/78113/en-us.
Anyway, that has nothing to do with my question.)

So my question becomes, if I have the following:

A1: =PI()
A2: =doit1()
A3: (filled in by macro doit2)
B3: =A2-A3=0 (FALSE!)

where in doit1() we have

doit1 = Range("A1")

and in doit2() we have

Range("A3") = Range("A1")

why do (that is, explain the mechanism by which) A2 and A3 have different
values, which is demonstrated by the fact that B3 results in FALSE?

And to be clear, I want the macro behavior to mimic the function behavior.
That is, I want the macro to assign the exact internal binary representation
found in A1.

So no change to function doit1 is warranted.

I'll eagerly <vbg> await Mike's response.

As I noted in another posting in this thread, I suspect that Jerry attempted
to explain this anomaly (failure to replicate the exact internal binary
representation) some time ago when I posted a similar observation about
copy-and-paste-special-value.

Unfortunately, I cannot find that response. And I don't recall if Jerry's
response was dispositive or merely speculation.

Well, I suspect I beat that horse to death. I only hope this has clarified
the question, if not for you, then for someone else.

----- original message -----

 

[Jerry W. Lewis]

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.279999999999999527044991509683313779532909393310546875")
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.2800000000000000266453525910037569701671600341796875
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/microsoft.public.excel.crashesgpfs/msg/3b31dabc74c22d38
but it appears that you (like me) do not use Excel 2007.

Jerry

 

[Bernard Liengme]

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?

 

[JoeU2004]

No amount of tapping the Increase decimal will give anything but
0.2800000000..... Am I missing something?

Yes, the fact that Excel will display only the first 15 significant digits,
rounding the 16th, whereas Jerry and I are looking at the internal binary
representation.

For example, when we enter 0.28 into A1 and format with 15 decimal places
(or more), Excel will display 0.280...0. But the exact internal value is
0.280000000000000,0266453525910037569701671600341796875.

(I use a comma to demarcate the first 15 significant digits.)

Now enter =0.28+2^-54 into A2. Excel will still display only 0.280...0.
But you can verify it is not the same as 0.28 by entering =A1-A2=0. It will
return FALSE. In fact, A2 is exactly
0.280000000000000,082156503822261583991348743438720703125.


----- original message -----

 

[Jerry W. Lewis]

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/microsoft.public.excel/msg/6efb95785d1eaff5
to see the decimal equivalent of the internal binary representation.

Cheers,
Jerry

 

[Bernard Liengme]

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]

New topic: Excel 2010

Your question is the first thing I have heard about Excel 2010. I would be
very interested in testing, but stopped getting replies from my former MS
contacts shortly after 2007 was released.

I was very vocal that 2007 should give concrete improvements (such as
improved statistical functions) to offset the re-learning curve of the new
interface; but apparently I had no impact on that point (though I would be
glad to see it in 2010). I did not expect the larger worksheet and formula
limits of 2007 to accomplish that offset with corporate IT decision makers:
- the larger limits would be viewed as encouraging uses of Excel that IT
would probably prefer to discourage, as better suited to other tools
- improved statistical functions would be viewed as improving the
reliability of existing uses, regardless of whether IT wanted those uses to
ultimately remain in Excel or not
I don’t know about all industries, but I am not aware of any companies in my
industry that have migrated.

Jerry

:
....

 

[JoeU2004]

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.

Well, that would come as no surprise to me. We are used to the fact that
for manual data entry, Excel stops converting after the 15th significant
digit, filling in the integer part with zeros as needed until the decimal
point.

Example #1.... Both 0.2799999999999995 (16 sig digits) and
0.279999999999999 (15 sig digits) have the same internal representation when
entered manually in Excel, namely the representation of the shorter
(0.279999999999999,02744463042836287058889865875244140625). In VBA, they
have different internal representations; the longer constant is represented
internally as 0.279999999999999,527044991509683313779532909393310546875.

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.

And that would have come as no surprise to me if I had written
Range("A3")="0.2799999999999995". I would expect that to have the same
behavior as manual data entry.

The surprise for me is that Range("A4")=CDbl("0.2799999999999995") also
seems to be treated like manual data entry (i.e. with the same limitations).
It has the same internal representation as 0.279999999999999, not the
internal representation of CDbl("0.2799999999999995").

But okay, I can see your point: it is a constant, not a value returned by a
formula. So arguably, it should be limited by the rules for constants.

However....

There are examples where the cell value assignment in a Sub does indeed
preserve the internal binary representation, even though it can __not__ be
entered manually as a constant in Excel.


Example #2.... When 0.1111111111111112 (16 sig digits) is entered into a
cell in Excel, it has the same binary representation as 0.1111111111111110
(15 sig digits), namely
0.111111111111110,993920902956233476288616657257080078125.

The same is true for the result of Range("A3")="0.1111111111111112" in VBA.

But the result of Range("A4")=CDbl("0.1111111111111112") has the same binary
representation as CDbl("0.1111111111111112"), namely
0.111111111111111,20208772007345032761804759502410888671875.

The same is true when we copy-and-paste-special-value A4 into A5: it
preserves __that__ binary representation, even though it cannot be entered
as a constant in Excel.


Example #3.... When 0.27999999999999897 (17 sig digits) is entered into a
cell in Excel, it has the same binary representation as 0.27999999999999897
(15 sig digits), namely
0.279999999999997,972732757034464157186448574066162109375.

The same is true for the result of Range("A3")="0.27999999999999897" in VBA.

But the result of Range("A4")=CDbl("0.27999999999999897") has the same
binary representation as CDbl("0.27999999999999897"), namely
0.279999999999998,971933479197105043567717075347900390625.

The same is true when we copy-and-paste-special-value A4 into A5: it
preserves __that__ binary representation, even though it cannot be entered
as a constant in Excel.


For assignments of the form Range(...)=CDbl(...) or any numeric expression,
I had previously speculated that, first, the binary representation is
converted to a numeric string as Excel would do for cell value formatted to
display 15 significant digits (i.e. effectively rounding the 16th
significant digit), then the numeric string is converted the same way that
data entry is (i.e. stop converting after 15 significant digits, filling in
the integer part with zeros as needed until the decimal place).

That rule fits Example #1.
0.279999999999999,527044991509683313779532909393310546875 is converted to
"0.280000000000000", which is then converted to the binary representation of
0.28, namely 0.280000000000000,0266453525910037569701671600341796875.

But that rule does __not__ fit Examples #2 and #3.

I have a contorted rule that seems to fit all three examples. But it flies
in the face of Occam's Razor. I am still looking for a simple explanation.

I assumed that it had been put to bed

Yes, I believe it has, at least for the purpose of this NG, even though I
still do not have a complete explanation to my satisfaction.

The only point was: the assignment of a value to a cell in a Sub may or may
not behave differently than the assignment of a value to a function name.
Sometimes (perhaps most of the time), the assignment in a Sub does not store
exactly the same binary representation.

This probably has little or no impact on the vast majority of users, almost
all of whom are probably oblivious to the internal binary representation
anyway, except when it surfaces as a numerical aberration that adversely
affects arithmetic and comparisons.


----- original message -----

 

[Jerry W. Lewis]

A follow-up to be sure that we have communicated:

This phenomenon is different than truncation on input. For example, all the
binary variants
of 0.283 are permitted as Excel constants. My vague recollection is that
this phenomenon
is more complex than simply replacing x with VALUE(x&"") when x&"" has no
more than
2 decimal places, but that was so many years ago that I cannot be certain.

Also, even though VBA and Excel both only display 15 digits, they are
different in the way
that they handle input with more than 15 digits:
- Excel always truncates input to 15 decimal digits before converting the
input to binary,
thus input of
0.12399999999999961 will be stored as the binary representation of
0.123999999999999, and then correctly displayed to 15 decimal digits as
0.123999999999999
- VBA converts the full input to binary, thus input of
0.12399999999999961 will be stored as the binary representation of
0.12399999999999961, and then correctly displayed to 15 decimal digits as
0.124. If you subsequently edit that line of code, then the underlying
value will actually
change to the binary representation of 0.124

As a result, if I am doing accuracy checks in a worksheet, then my reference
values would
be given to full precision by =D2F("0.12399999999999961")

If I am doing numerical work in VBA that involves floating point constants,
then I either use
CDbl("0.12399999999999961") or maintain the code in a text editor to be
insure that the
full precision of the numeric constants is not lost.

Jerry

 

[JoeU2004]

A follow-up to be sure that we have communicated:

I agree that this is a good summary of the points that I tried to make.

This phenomenon is different than truncation on input.

Right.

Initially, you had written: "all the binary variants for the 15 digit
display collapse into a single binary value for each of the 'unpermitted'
constants", and "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".

I understood you to be asserting that the problem relates to how Excel
handles entering constants into cells manually; that the "unpermitted
constants" are constants with digits beyond the first 15 significant digits.

I refuted that assertion with examples "where the cell value assignment in a
Sub does indeed preserve the internal binary representation, even though it
can __not__ be entered manually as a constant in Excel".

If, instead, your term "unpermitted constants" is meant to refer to some
list or category of constants, I would be very surprised if that is the
case. It is more likely explained by an algorithm (or heuristic or rule).
If you have a theory about the algorithm, I'd be interested in hearing it.

all the binary variants of 0.283 are permitted as Excel constants.

Fascinating example.

I believe you are trying to say that the following preserves the VBA binary
representation for all constants that are displayed as 0.283000000000000
when formatted to 15 significant digits, namely for all "i" equal to -8 to
9:

Range("A1") = 0.283 + i*2^-54

I agree. And I believe the examples for "i" equal to 1 to 9 contradict even
the contorted rule that I had devised to explain all this. Back to the
drawing boards!

(Counter-example: CDbl("'0.28000000000000047"), 17 sig digits, whose binary
representation is not preserved when assigned to a cell in VBA.)

Nitpick: I would not say that these "variants" are "permitted as Excel
constants" per se. To me, that means we can enter them into a cell as a
constant. I know that is not what you mean, because I know you understand
that for manual data entry, Excel "truncates input to 15 [significant]
digits before converting the input to binary". (You said that so much
better than I did.)

I would say, as I presume you mean, that the assignment of these values to a
cell in a macro preserves the VBA binary representation.


----- original message -----