Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding issue | Excel Discussion (Misc queries) | |||
Rounding issue | Excel Discussion (Misc queries) | |||
Rounding issue | Excel Worksheet Functions | |||
Excel Rounding Issue | Excel Discussion (Misc queries) | |||
Another rounding issue | Excel Worksheet Functions |