Excel has an error: 111,111,111*111,111,111 the answer is wrong!!

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

Guest

When you try to multiply 111,111,111*111,111,111 using excel spreadsheet, the
answer comes out wrong.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...6e39d73&dg=microsoft.public.excel.crashesgpfs
 
Excel, like nearly every other computer program, uses the IEEE
8-byte Double Precision Floating Point data type. This data type
is limited to 15 digits of precision. Anything past that is
rounded.

It isn't a bug in Excel. It is the result of a limitation in the
industry-standard data type.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




message
news:[email protected]...
 
Excel's answer is closer than you may realize.

Mathematically 111,111,111*111,111,111 is 12,345,678,987,654,321. As Chip
has noted, Excel (like almost all software) represents numbers in binary, but
12,345,678,987,654,321 would require more than IEEE standard double precision
to represent. The result therefore gets rounded to 12,345,678,987,654,320
which is the closest possible IEEE double precision number. If you format
the cell to show you 17 digits, you will see 12,345,678,987,654,300, not
because of less accuracy, but because Excel behaves as documented (see Help
for "specifications"), and will not display more than 15 significant digits.
That more is actually there but not displayed can be revealed by subtracting
12,345,678,987,654,300 from the result; you will get 20 instead of 0.

To see more than 15 digits of what Excel natively stores, see my D2D()
function at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465

If you truly need more than 15 digit precision in your calculations, then no
IEEE standard double precision software will do; you might try Excel add-ins
like
http://digilander.libero.it/foxes/index.htm
http://precisioncalc.com/
Or switch to something capable of symbolic math like Maple, Mathematica,
MacSyma, or Maxima.

Jerry
 
Back
Top