View Single Post
  #4  
Old November 13th, 2009, 01:33 AM posted to microsoft.public.excel.misc
Joe User[_2_]
external usenet poster
 
Posts: 757
Default Something is not right

"Dave Peterson" wrote:
Excel only keeps track of 15 significant digits.


Not quite right.

Excel __displays__ only 15 significant digits.

But it "keeps track" of however many digits the binary format and arithmetic
computation permits [1].

In fact, 111111111*111111111 actually results in 12345678987654320
internally. This is evident by the fact that =A1-12345678987654300 results
in 20, assuming that A1 is =111111111*111111111 [2].

So Excel's computation of 111111111*111111111 is only off by one in this
particular case.

But in general, I think that "Bob I" says it best: "Nothing wrong except
expectations are too high".

For some details (albeit misstated in places), see
http://support.microsoft.com/kb/78113.


-----
Endnotes

[1] What the "binary arithmetic computation permits" can vary from one
implementation to another, even on the same computer. This is why
occassionally Excel and VBA will have different results for the same
expression.


[2] It's not always so easy to determine the difference between the
displayed and actual internal values. For example, the result of
1111111111*1111111111 (10 digits each) is displayed as 1234567900987650000,
and it is stored internally as 1234567900987654400.
But =A1-1234567900987650000 is 4352, not 4400.

Ironically, a difference of 4352 would be closer to the difference between
the displayed value and the correct result computed by hand, which ends in
4321. But that is coincidental -- and misleading in this case.


----- original message -----

"Dave Peterson" wrote in message
...
Excel only keeps track of 15 significant digits.



fruitchunk wrote:

I was told that 111,111,111 x 111,111,111 = 12,345,678,987,654,321
However when I run in excel =111111111*111111111 I get 12345678987654300
Something is not right, it should definitely end with a 1.


--

Dave Peterson