Re: Custom Format Limitation
- From: "Chip Pearson" <chip@xxxxxxxxxxxx>
- Date: Mon, 3 Dec 2007 13:46:49 -0600
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" <vals@xxxxxxx> wrote in message news:eFzIVNeNIHA.5860@xxxxxxxxxxxxxxxxxxxxxxx
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.
.
- References:
- Custom Format Limitation
- From: David Steed
- Custom Format Limitation
- Prev by Date: Function or VBA
- Next by Date: Re: Time Cycle
- Previous by thread: RE: Custom Format Limitation
- Next by thread: Re: Custom Format Limitation
- Index(es):
Relevant Pages
|
