How do I keep a 17 digit number from ending in "0" in excel?

C

creel28270

I am trying to enter 17 digit numbers in an excel spreadsheet, but the sheet
automatically changes the last digit to a zero.
 
R

Rick Rothstein \(MVP - VB\)

Either pre-format the cell(s) as Text or enter your numbers with a leading
apostrophe (it won't show up in the cell).

Rick
 
H

Harald Staff

Ben

There's 15 digit precision in Excel. Which I believe has something to do
with 32 bits resolution.

Best wishes Harald
 
J

Jerry W. Lewis

IEEE double precision (used by Excel) uses 64 bits to represent floating
point numbers. 52 bits (effectively 53 bits for numbers > 1E-307 in
magnitude) go to repesenting the value (the rest go to the exponent and
sign). This is sufficient to approximate 15 decimal digit numbers, but
requires 17 decimal digits to uniquely identify the binary represention.
Excel's documented 15 decimal digit limitation is a design decision to avoid
explaining why some 17 decimal digit numbers would otherwise change value
immediately on input. Some other IEEE compliant packages will allow you to
see and directly manipulate the full 17 decimal digits.

Jerry
 
H

Harald Staff

Ah. Thank you Jerry.
Best wishes Harald

Jerry W. Lewis said:
IEEE double precision (used by Excel) uses 64 bits to represent floating
point numbers. 52 bits (effectively 53 bits for numbers > 1E-307 in
magnitude) go to repesenting the value (the rest go to the exponent and
sign). This is sufficient to approximate 15 decimal digit numbers, but
requires 17 decimal digits to uniquely identify the binary represention.
Excel's documented 15 decimal digit limitation is a design decision to
avoid
explaining why some 17 decimal digit numbers would otherwise change value
immediately on input. Some other IEEE compliant packages will allow you
to
see and directly manipulate the full 17 decimal digits.

Jerry
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

16 digit number entry changes last digit to 0 1
Excel Changes My Last Digit to Zero 1
Excel 2003: Cell rounds 16 digit numbers- How to advoid rounding? 1
last number defaluts to zero 2
Entering a 16 digit number 1
Extracting 7 digit number from alphanumeric string 10
"Bug" in number format for long number entries? 7
Excel is changing the last digit to 0 2

Top