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.

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.

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.

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.

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.