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.

Excel v Windows Calculator precision

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jcombe@gmail.com - 19 Sep 2006 10:57 GMT
I am surprised to find that Windows Calculator (calc.exe) seems to have
a higher precision than Excel. For example 2^64 in Excel yields
18446744073709600000. In Windows calculator I get 18446744073709551616.
The same restriction exists in OpenOffice.org too, although KSpread on
Linux can go to 2^997 before reporting "INF" although of course it is
impossible to display all the numbers on one line.
Bob Phillips - 19 Sep 2006 11:28 GMT
Excel uses 15 digit precision, which is quite common.

Here is a nice neat explanation posted by Jerry Lewis recently

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/b106871cf92...

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I am surprised to find that Windows Calculator (calc.exe) seems to have
> a higher precision than Excel. For example 2^64 in Excel yields
> 18446744073709600000. In Windows calculator I get 18446744073709551616.
> The same restriction exists in OpenOffice.org too, although KSpread on
> Linux can go to 2^997 before reporting "INF" although of course it is
> impossible to display all the numbers on one line.
Jerry W. Lewis - 19 Sep 2006 13:19 GMT
Windows calculator (WC) carries more accuracy than does Excel.  Windows
calculator displays up to 32 digits.  Excel uses binary double precision,
which generally gives 15-17 digit precision (Excel limits the display to 15
digits).  If I were to guess without testing, I would guess that WC uses
binary quad precision.  

That said, simple binary calculations like =2^64 are calculated exactly in
Excel, even though Excel will only display 15 digits.  You can use my D2D
function from
 http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
to display in Excel the exact decimal value of 2^64 as
 1.8446744073709551616E19

Moreover, you can use D2D in Excel to display 2^107 as
 1.62259276829213363391578010288128E32
whereas there is no way AFAIK to get the 33rd digit from WC.

Jerry

> I am surprised to find that Windows Calculator (calc.exe) seems to have
> a higher precision than Excel. For example 2^64 in Excel yields
> 18446744073709600000. In Windows calculator I get 18446744073709551616.
> The same restriction exists in OpenOffice.org too, although KSpread on
> Linux can go to 2^997 before reporting "INF" although of course it is
> impossible to display all the numbers on one line.
 
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.