"MLT" wrote:
I need to enter a percent (10.638651949780948)
but it keeps rounding the number to
(10.638651949780900). No matter what I do I
can't get the last 2 digits to stick (48) to stop
changing ot (00).

I cannot imagine a situation where it makes a difference (and I have tried
several), but there are ways that you can get closer to the value you want.

BTW, do you want 10.638651949780948%, or do you want 10.638651949780948? It
makes a difference in the result.

The most straight-forward way (IMHO).... Use the following macro:

Function myCdbl(s As String) As Double
myCdbl = CDbl(s)
End Function

This works better than Excel VALUE() or simply entering the constant
manually because VBA converts the entire string of digits, whereas Excel
stops conversion after the 15th significant digit. Excel does not even try
to round the 16th significant digit.

In Excel:

=myCdbl("10.638651949780948")

results in the exact value
10.63865194978094841360416467068716883659362792968 75 -- about 0.41E-15 higher
than you require, but the next closest value (-2^-49) is 1.36E-15 less than
what you require.

However:

=myCdbl("0.10638651949780948")

results in the exact value
10.63865194978094869115992082697630394250154495239 2578125% -- about
0.691E-17 higher than you require. The next closest value (-2^-56) is about
0.697E-17 less than what you require.

Note: You could write myCdbl("10.638651949780948")% instead. But even
though that results in the same exact value in this case, in general it may
be less accurate because some of the computation is performed by Excel after
converting the Cdbl() result to a 64-bit floating point representation.

Instead of using VBA Cdbl(), you could enter the values above directly into
Excel, to wit:

=10.6386519497809 + 27*2^-49

=10.6386519497809% + 35*2^-56

Caveat: The additional power-of-2 factors are tailored for the particular
constant, 10.6386519497809(%). They will not work (usually) with other
constants, although some other power-of-2 factors could be determined on a
case-by-case basis. This is why the VBA Cdbl() approach seems more
straight-forward.

It is wrong to say that Excel (or IEEE 64-bit floating point) is only
capable of maintaining 15-significant-digit precision. The more correct
statement is: that is the maximum precision that can be represented
consistently for numbers of any magnitude.

Moreover, Excel will format only 15 significant digits for display, and it
will convert only the first 15 significant digits for data entry (and values
stored by macros, with some "anomalies" -- I use the term advisedly).

But even the 15-significant-digit presentation is not represented exactly
internally (with rare exception). IEEE 64-bit floating point represents
numbers by 53 consecutive powers of 2 ("mantissa")l The exact value is the
sum of those powers of 2 (some may be zero) times a power of 2 ("exponent").