16 digit non decimal number

[Mark L]

Seems simple, write any 16 digit non decimal number in a cell. For
some reason when I do this in my spreadsheet, Excel keeps changing the
last digit to a 0 (zero). Nothing I do can get a larger number to
stick. 17 digits, 18...., any digit more than the first 15 digits
(from left to right) turns into a zero.

2255225522552255 becomes 2255225522552250
2255225522552252 becomes 2255225522552250
2255225522552259 becomes 2255225522552250
22552255225522557 becomes 22552255225522500

It's not rounding the number.

I can format the number as text and it will work fine. I can put a
space like a credit card number
2255 2255 2255 2255 and it stays but if I try 7777777777777777 I'll
get 7777777777777770.

Anyone know how I can get all 16 digits to remain? General format
yields an exponential number and a number with 1 decimal works
7777777777777777 becomes 7777777777777777.0 but zero decimals becomes
7777777777777770. Seems like it should be simple. Can anyone explain
please? Can this be a bug?

 

[J.E. McGimpsey]

Seems simple, write any 16 digit non decimal number in a cell. For
some reason when I do this in my spreadsheet, Excel keeps changing the
last digit to a 0 (zero). Nothing I do can get a larger number to
stick. 17 digits, 18...., any digit more than the first 15 digits
(from left to right) turns into a zero.

2255225522552255 becomes 2255225522552250
2255225522552252 becomes 2255225522552250
2255225522552259 becomes 2255225522552250
22552255225522557 becomes 22552255225522500

It's not rounding the number.

I can format the number as text and it will work fine. I can put a
space like a credit card number
2255 2255 2255 2255 and it stays but if I try 7777777777777777 I'll
get 7777777777777770.

Anyone know how I can get all 16 digits to remain? General format
yields an exponential number and a number with 1 decimal works
7777777777777777 becomes 7777777777777777.0 but zero decimals becomes
7777777777777770. Seems like it should be simple. Can anyone explain
please? Can this be a bug?

It's not a bug. If you look up "specifications" in XL Help, you'll
find that XL's precision limit is 15 decimal digits, which is pretty
standard for IEEE double precision floating point calculations. You
can find a lot more detail at

http://cpearson.com/excel/rounding.htm

Do you need to do math on the 16 digit number? If so, you'll either
have to use a different application, or do some convoluted string
math. There are a couple of add-ins out there that claim to add up
to 200 digits of precision (why, I don't know), but AFAIK, they're
for WinXL only. But I may be wrong.

If you're working with credit card numbers, preformatting as text is
probably the way to go.

Another way is to prefix each entry with an apostrophe

'2255225522552255

which tells the parser to treat the entry as text. After you hit
Return, the apostrophe will display in the Formula Bar, but not in
the cell.

 

[Jim Gordon]

Hi Mark,

Numbers in Excel are calculated to 15 digits of precision. Rounding will
occur when more than 15 digits are used.

The number of text characters in a cell is limited to 256.

Notice that the limits are in powers of 2. So are other limitations, such as
the number of columns and number of rows in a worksheet. Computers use two
digits (zero and one) when performing calculations, so lots of computational
power is constrained by that fact. Expanding these limitations would
certainly be possible, but there would be a significant slowdown in
calculation speed due to the increased complexity of calculation.

In your case, you could parse the complete number into two portions, perform
calculations on each half then combine the results into a single result that
would be concatenated and displayed as text. You'd have to give the
calculations considerable thought to minimize rounding errors.

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Search for help with the free Google search Excel add-in:
<http://www.rondebruin.nl/Google.htm>

 

[J.E. McGimpsey]

The number of text characters in a cell is limited to 256.

Actually, it's 32,767 (2^15 - 1), though, according to Help's
Specifications page, only 1024 (2^10) can be displayed at a time.