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.

Too many significant places required to display that. Nothing wrong
except expectations are too high.

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.

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

"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

So no one misinterprets your statement, results are not random. When
evaluating a complicated expression, intermediate results are produced. The
final result of the calculation depends on the storage precision for those
intermediate results, which can depend on the implementation.

For instance, in VBA, if all variables in the calculation are explicitly
declared double, then intermediate results appear to be stored in the 10-byte
extended precision of the math processor. In the following VBA code, the
only difference between the calculations for c and cd are that the variables
contributing to cd are explicitly double. Without extended precision
intermediate storage, the multiplication underflows to zero, before the
division brings it back into double precision range.

Sub tryit()
Dim a, b, ad As Double, bd As Double
a = 1E-300: b = 1E-30: ad = 1E-300: bd = 1E-300:
c = a * b / a
cd = ad * bd / ad
MsgBox (c & " " & cd)
End Sub

Jerry

"Joe User" wrote:
....
[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.
....