Excel v Windows Calculator precision

J

jcombe

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

Bob Phillips

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


--
HTH

Bob Phillips

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

Jerry W. Lewis

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
 
Top