Format a cell as number and set decimal places to 0.

Then enter 1818181818181818 in a cell and hit enter...

Excel then CHANGES to the number to:

1818181818181810 ???? WHAT ???

This can't be right can it ??, what am I missing ?

Mike,

Excel, like nearly all other computer programs, use
industry-standard Double-Precision Floating Point numbers, which
are limited to 15 places of precision. Thus, you can't accurately
store larger numbers or numbers with more decimal places. See
www.cpearson.com/excel/rounding.htm for more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Mike One" <Mike wrote in message
...
Format a cell as number and set decimal places to 0.

Then enter 1818181818181818 in a cell and hit enter...

Excel then CHANGES to the number to:

1818181818181810 ???? WHAT ???

This can't be right can it ??, what am I missing ?

I should have added that if you need to store a larger number but
not do math with it (e.g., a credit card number), precede the
number with an apostrophe. The apostrophe will not show in the
cell, but will show in the formula bar.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Mike One" <Mike wrote in message
...
Format a cell as number and set decimal places to 0.

Then enter 1818181818181818 in a cell and hit enter...

Excel then CHANGES to the number to:

1818181818181810 ???? WHAT ???

This can't be right can it ??, what am I missing ?

Hi Mike,

Excel's limitation of 15 significant digits is not caused by Excel or
Microsoft. All other spreadsheet programs, including Lotus 123 and Corel
Quattro Pro, have the same limitation. The limitation is enforced by the
microprocessor. All Intel, Intel-compatible, Macintosh, and UNIX
microprocessors have the same limitation.

The 15 significant digit limitation is part of an industry standard called
"IEEE 754", which was created to achieve faster processing by sacrificing
precision. IEEE 754 was ratified in 1985, by which time it had already
become a de facto standard.

To do math in Excel on numbers with arbitrarily large numbers of significant
digits, you'll need an Excel add-in that does its own high-precision
arithmetic in software and ignores IEEE 754. That's slower than using the
microprocessor to do the arithmetic, though maybe not as slow as
microprocessors that were popular in 1985 when IEEE 754 was ratified.

My Excel add-in xlPrecision 2.0 allows up to 32,767 significant digits. The
worksheet functions it provides do all arithmetic in software and ignore
IEEE 754. You can download the free edition here and use it as long as you
wish:

http://PrecisionCalc.com


Good Luck,

Greg Lovern
http://PrecisionCalc.com
Eliminate Hidden Spreadsheet Errors



"Mike One" wrote in message
...
I find this ridiculous and a bug. It will really screw up your excel sheet
when you are using CC numbers (which I found the issue with). I'm
currently
using the 'quote' workaround but I can't believe a spreadsheet programme
would just do this without warning. Microsoft - BAD DOG!

"Mike One" wrote:

Format a cell as number and set decimal places to 0.

Then enter 1818181818181818 in a cell and hit enter...

Excel then CHANGES to the number to:

1818181818181810 ???? WHAT ???

This can't be right can it ??, what am I missing ?

Greg Lovern wrote:

Hi Mike,

Excel's limitation of 15 significant digits is not caused by Excel or
Microsoft. All other spreadsheet programs, including Lotus 123 and Corel
Quattro Pro, have the same limitation. The limitation is enforced by the
microprocessor. All Intel, Intel-compatible, Macintosh, and UNIX
microprocessors have the same limitation.

The 15 significant digit limitation is part of an industry standard called
"IEEE 754", which was created to achieve faster processing by sacrificing
precision. IEEE 754 was ratified in 1985, by which time it had already
become a de facto standard.

Your comments are correct in principle, but the details are a bit more
complicated than you imply (as you doubtless understand from the work
involved in writing xlPrecision).

The IEEE standard, governs binary representation, not decimal. Since
1818181818181818 is exactly representable in IEEE double precision, it
may display fully in other spreadsheets (I would have to try it). It
takes 17 decimal digits to uniquely specify an IEEE double precision
number, but some 16 decimal digit numbers cannot be represented.
Microsoft imposed a 15 decimal digit limit on inputs and on displayed
output, because to avoid questions like "why when I enter
9007199254740993, does it get changed to 9007199254740992?" as would
happen with an IEEE double precision program that displays more than 15
decimal digits.

The real surprise is in how Excel chooses to convert 16+ digit input
into a 15 digit displayed value. If I were writing the interface with a
15 decimal digit display limit, I would have used the entire input to
store the closest IEEE double precision approximation to that input in
the cell, then I would display the cell contents to 15 decimal digits.
The result would be that 1818181818181818 would have been exactly
stored, and would display as 1818181818181820, just as the VBA editor
does. Instead Excel first truncates the input to 15 digits, then stores
the IEEE approximation to that 15 digit number. The result is
unnecessarily lost precision that is not even rounded properly.

You can verify that 1818181818181818 can be exactly represented by
calculating it as a result =1818181818181810+8. The value will display
as 1818181818181820, but is stored with full precision as you can verify
by subtracting 1818181818181810.

Regardless, the fact remains that there is rarely (if ever) any reason
to do math on credit card numbers or account numbers, and hence the
simplest and fastest solution is to enter them as text.

Jerry