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...
 
Top