"Bug" in number format for long number entries?

[Guest]

Hi,

I was keeping track of my credit card numbers with a spreadsheet. And when
I enter a cc numer such as a visa card the last digit gets rounded to a "0"
No matter what I tried this happened. But it doesn't happen in Excel 2003!

I checked to be sure I was using a correct format (number entry in the cell
and column I was in). I even tried chnaging the format to currency, etc, but
nothing would work. The last digit no matter what I enter gets changed to a
zero.

This seems to happen beginning with the 16th number and actually continues
if you enter more than 15 numbers (i.e. 16 numbers, 17 numbers, etc.)

Try it ! Any ideas.
Tx Chippo

 

[Guest]

15 is the limit for excel to display properly AFAIK...format as text and you
will be right

 

[Guest]

Have tried to make the entry a FORMULA by placing an equals = sign before the
entry, and that did not work. Then I formatted the column for text format
and it places it in exponential format. Not good. that it would work. No
matter how you put the numer (i.e. as a text entry or as a formula) when you
get to the 16th digit, it reverts to a 0.

Try this: 12345678910123456

and you will see that the final 6 becomes a 0

Even if you place additional numbers they all show, but revert to 0s for
example
1234567890123456789 will becomne 1234567890123450000 !

The only way I could get it to work is by placing " in front of the numbers
(the other form of text entry). e.g. "1234567890123456789 This works, but
is really not all that satisfactory.

Surely Excell 2007 must have space for more than 16 digits!!! Can they
really be serious? They have expanded capacity in other significant ways,
so why not this?

Maybe I am doing somethign wrong ? ? ?

Tx Chippo

 

[Peo Sjoblom]

We know that it will truncate every digit after 15th to a zero, that is
what Paul was saying to you. You need to format as text before you type in
the numbers, it will not work on existing entries without editing the cell
by selecting it and pressing F2 enter but of course it will not change back
the zeros to what you originally typed. If you are just storing numbers like
serial numbers or credit cards you can either precede each entry with an
apostrophe (not visible in the cell) or preformat as text. Then type in the
numbers. If you want to calculate these kind of numbers (which I am pretty
sure you don't want to do) then you can't use Excel, there might be third
part commercial add-ins but it is probably better to get more advanced
software like Mathematica which is a mere 2500 dollars or something like
that

 

[Guest]

Thanks Peo, Appreciate your thoughtful explanation. Strikes me as strange
that that is the limit!
Chippo

 

[Chip Pearson]

Strikes me as strange
that that is the limit!

See the "IEEE Floating Point Standard" section on
http://www.cpearson.com/excel/rounding.htm for the technical reasons for
that particular limit. Once you learn how numbers are actually stored in
binary format, the limitation makes sense.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

To represent 1234567890123456789 as a binary floating point number would
require 60 bits of information, but the IEEE standard for double precision
(see Chip Pearson's link) only suports 53 bits. Rather than explain why your
value changed on entry to 1234567890123456768 (the closest 53 bit
approximation), MS simplified the rule to "no more than 15 decimal digits",
and clearly documented it in Help (in Excel 2000, search for topic "Microsoft
Excel specifications" subtopic "Calculation specifications").

Jerry

 

[Guest]

Chippo,
Just type in your card number with dashes ie:
5580-9986-8845-4713

That way it won't read the card number as a number but rather just plain
text and does not round or transfer into exponets.
Tyler