#1   Report Post  
Posted to microsoft.public.excel.misc
MLT MLT is offline
external usenet poster
 
Posts: 5
Default Rounding Issue

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).
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,819
Default Rounding Issue

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

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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,722
Default Rounding Issue

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default Rounding Issue

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

Jim Thomlinson


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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 905
Default Rounding Issue

"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").
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rounding issue Haz Excel Discussion (Misc queries) 3 January 2nd 09 12:33 PM
Rounding issue [email protected] Excel Discussion (Misc queries) 4 August 28th 07 07:23 PM
Rounding issue Kimo Excel Worksheet Functions 5 July 31st 07 12:37 AM
Excel Rounding Issue AccentStripe Excel Discussion (Misc queries) 3 May 18th 07 09:15 PM
Another rounding issue Biff Excel Worksheet Functions 2 June 20th 05 01:10 PM


All times are GMT +1. The time now is 04:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"