Rounding problem

M

mark_timberlake

I have a series of 16 and 18 digit numbers that I am trying to store and work
with.
If for example I have the number 1234123412341234 and I leave the field
unformatted I get '1.23412E+15' which is fine. If I change the cell
formatting to 'number' w/o decimals I get '1234123412341230'. What has
happened to my last '4'? Why is it now a cheeky '0'? This seems to happen
with all digits after and including the 16th.

Any help appreciated.

Mark
 
J

Jerry W. Lewis

IEEE double precision (used by Excel and almost all other numerical software)
can exactly represent all 15 digit integers, but only some integers with more
than 15 digits. Rather than repeatedly explain why 9876543210123457 turns
into 9876543210123456, MS chose to display only 15 digits, as documented in
Help.

When you enter a number in a cell, Excel truncates it to 15 significant
digits before converting to binary, so to accurately enter more than 15
digits, you have to write it as a formula involving constants of no more than
15 digits each, or else poke it from VBA using CDbl("1234123412341234").

To see more than 15 digits of the value that was actually stored from your
formula, try the conversion functions posted at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465

Jerry
 
Top