Why is Excel changing the last 2 digits of a 17 digit num to 00.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I enter a 17 digit number in a cell in Excel, the last 2 digits turn to
00 when I leave the cell. Format - Cell does not have a setting to stop this
'feature'. How do I make Excel recongize the large number?
 
When I enter a 17 digit number in a cell in Excel, the last 2 digits turn to
00 when I leave the cell. Format - Cell does not have a setting to stop this
'feature'.
Click to expand...

Excel Specifications and Limits:

Calculation specifications

Feature Maximum limit
Number precision 15 digits

How do I make Excel recongize the large number?
Click to expand...

If the number does not require calculation (e.g. if it is an ID number, or a
credit card number, for example) you can enter it as TEXT. Either pre-format
the cell as text; or precede your entry by a single quote '12345678901234567

If you require mathematical operations with more than 15 digits of precision,
you will need to use a different tool.


--ron
 
Some additional comments (quite possibly more detail than you really
wanted):

Excel (and almost all other software) does binary math. The binary
storage format (IEEE double precision) Excel uses is discussed at
http://www.cpearson.com/excel/rounding.htm

In IEEE double precision, every 15 digit number can be distinguished,
but 17 digits are required to uniquely specify a particular binary
floating point number. Presumably Excel only displays 15 digits to
avoid having to answer questions like "why, when you enter
12345678901234567 do you get back 12345678901234568?" (the closest
double precision approximation).

When you enter more than 15 digits in Excel, the number is truncated to
15 digits before conversion to binary, even if that results in incorrect
rounding (e.g. 12345678901234567 will be converted to 12345678901234500
which displays as 1.23456789012345E16).

When you enter more than 15 digits in VBA, the number entered will be
directly converted to binary (e.g. 12345678901234567 will be converted
to 12345678901234568 which displays as 1.23456789012346E16).
Unfortunately, if you edit the line again, then Excel will reconvert
from the displayed number to binary, so the value will change from
12345678901234568 to 12345678901234600 which still displays as
1.23456789012346E16 but is a less accurate approximation to what you
originally entered.

If you reliably want the most accurate numeric approximation to a more
than 15 digit number, use something like CDbl("12345678901234567") in VBA.

You can use VBA to put more accurate approximations into Excel. Thus if
you use the VBA line
[A1] = CDbl("12345678901234567")
and directly enter 12345678901234567 in A2, or equivalently use the cell
formula
=VALUE("12345678901234567")
then the cell formula
=A1-A2
will return 68, showing that VBA gave you the more accurate binary
approximation.

Jerry
 
Back
Top