Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 2nd 05, 08:23 PM
Pam Coleman
 
Posts: n/a
Default number formatted cell

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?

  #2   Report Post  
Old June 2nd 05, 08:41 PM
bj
 
Posts: n/a
Default

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.

"Pam Coleman" wrote:

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?

  #3   Report Post  
Old June 2nd 05, 08:44 PM
TomHinkle
 
Posts: n/a
Default

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 (#).


"Pam Coleman" wrote:

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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy a number from one cell to another depending on first number marcia driscoll Excel Worksheet Functions 3 May 3rd 05 08:30 PM
How do I format a cell for a custom part number? PJ Excel Discussion (Misc queries) 4 March 3rd 05 03:57 AM
How do I get a letter in one cell ito equal a number in anotherl.. Dave Cadey Excel Discussion (Misc queries) 4 February 25th 05 02:37 PM
reference the result of a formula in a text formatted cell jpwinston Excel Discussion (Misc queries) 1 February 7th 05 05:33 PM
multiply by actual number in cell CJ Cerezo Excel Worksheet Functions 3 November 29th 04 09:43 PM


All times are GMT +1. The time now is 05:14 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017