Rounding Issue

[MLT]

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

 

[Bob I]

You won't get that many digits of precision from Excel. You would need
some other software or addin.

 

[Luke M]

XL can not store more than 15 digits (see XL limits and specifications). If
you need to display that many digits, you will need to first format the cell
to text.

Due note that this only is for display values, as soon as you try to use it
in a calculation, XL will truncate the excess digits.

 

[Jim Thomlinson]

That is more a limit of your computer than it is of XL. The IEEE standard for
calcuation is to a maximum of 15 significant digits. If you want to go beyond
that you need to use an addin which will add a fair bit of overhead to your
calculations.

Curious... why do you need more than 15 significant digits of accuracy. With
that kind of accuracy you can measure the distance from here to the sun to
less than a millimeter.

 

[Joe User]

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.6386519497809484136041646706871688365936279296875 -- 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.638651949780948691159920826976303942501544952392578125% -- 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").