number formatted cell

[Guest]

If I am typing in 19 numbers in a cell (that has been formatted to number) to
keep track of account numbers, it puts zeroes at the end and only 15 numbers
are showing. If I go into another cell and choose text first before typing
and then type my numbers you can see all the numbers, but when I try to go
and change the number formatted cell to text the number still does not change
unless I retype the number. Is this the way this works?

 

[Guest]

yes
excel only keeps track of the first 15 digits in a number, if you entered
more digits as a number,they are now lost.

 

[Guest]

yes... with floating point numbers the way most computers store them (at the
level you and I would care not to think about ;-) ) space to store them is
regimented. I copied and pasted the definition of the double data type from
excel VBA. Generally speaking, a number in excel can hold 15 significant
digits...

so once you type in the number and excel determines it needs to be stored as
a numeric, and digits after the 15th are gone.. truncated.

It sort of makes sense.. if you're doing a calculation with the number, any
digit past the 16th would not impact the result much. BUT it does make for
the entry of numerics used for identification tricky.

My basic rule of thumb (in databases and spreadsheets ) is if you are NOT
going to do a calculation with the number ( +, -, *, / ) force it to be text.

hope this helps..




Double (double-precision floating-point) variables are stored as IEEE 64-bit
(8-byte) floating-point numbers ranging in value from -1.79769313486231E308
to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324
to 1.79769313486232E308 for positive values. The type-declaration character
for Double is the number sign (#).