Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / September 2006

Tip: Looking for answers? Try searching our database.

Rounding problem

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
mark_timberlake@hotmail.com - 11 Sep 2006 13:00 GMT
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
Jerry W. Lewis - 11 Sep 2006 13:54 GMT
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

> I have a series of 16 and 18 digit numbers that I am trying to store and work
> with.
[quoted text clipped - 7 lines]
>
> Mark

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.