Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Format Limitation
There appears to be a limitation on the number of # characters you can use
in a custom format. Is this true? We keep running out of calculated results at 15 with the 16th zero. Any help appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Format Limitation
Numbers have a fundamental limitation of 15 digits. To get more than 15
digits, precede the value with an apostrophe (single quote) -- Gary''s Student - gsnu2007b "David Steed" wrote: There appears to be a limitation on the number of # characters you can use in a custom format. Is this true? We keep running out of calculated results at 15 with the 16th zero. Any help appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Format Limitation
David,
Excel, like nearly all software, uses for format called "Double Precision Floating Point" to store numbers. This is an industry-wide standard, kept by the Institute Of Electrical And Electronic Engineers (IEEE) -- it is in now way unique to Excel or to Microsoft product in general. All software must cope with the IEEE standard in one way or another. This format allows for only 15 digits of precision. Anything outside that ranges is rounded away. Since nearly all numbers cannot be stored *exactly* in binary format, they are subject to rounding. Think about it this way, the number expressed by 1/3 cannot be fully stored accurately with a fixed number of decimal places. No matter how far out you carry 0.33333.... it is still an approximation of the real value 1/3. Computers work essentially the same way, but in binary not decimal. This rounding approximation and the limitation of 15 digits of precision are well known to programmers, and there are ways, in both formulas and code to overcome the issues. They are not bugs -- everything was designed to work this way. If you need to store numbers with more than 15 digits of precision, but not to arithmetic with those numbers (e.g. phone numbers, credit card numbers, etc), you can precede the entry in the cell with an apostrophe. This tells Excel to treat the content literally without translation. Of course, if you tried to do arithmetic with those numbers, you go back in to the round issues. To prevent rounding errors, you can writing some. For example, If Range("A1").Value - Range("A2").Value = 0 Then ' this may not be exactly true: A1 - A2 may be 0.00000000000001 you can write code like If Abs(Range("A1").Value - Range("A2").Value) < 0.000000001 Then ' treat as equal See http://www.cpearson.com/Excel/Rounding.htm for a full description of these issues. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "David Steed" wrote in message ... There appears to be a limitation on the number of # characters you can use in a custom format. Is this true? We keep running out of calculated results at 15 with the 16th zero. Any help appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Format Limitation
Excel is capable of using only 15 significant digits.
After 15 digits, zeros will appear. More can be shown if you enter the digits as text but then you could not calculate with them. Gord Dibben MS Excel MVP On Mon, 3 Dec 2007 12:32:58 -0700, "David Steed" wrote: There appears to be a limitation on the number of # characters you can use in a custom format. Is this true? We keep running out of calculated results at 15 with the 16th zero. Any help appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Format Limitation
does =10^-14 help...
"David Steed" wrote: There appears to be a limitation on the number of # characters you can use in a custom format. Is this true? We keep running out of calculated results at 15 with the 16th zero. Any help appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a Custom Format to Format 11168 as 111.68? | Excel Discussion (Misc queries) | |||
Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
custom format or conditional format? | Excel Discussion (Misc queries) | |||
how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
Format a cell with a custom number format | Excel Worksheet Functions |