A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Something is not right



 
 
Thread Tools Display Modes
  #1  
Old November 12th, 2009, 10:34 PM posted to microsoft.public.excel.misc
fruitchunk
external usenet poster
 
Posts: 33
Default Something is not right

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.



Ads
  #2  
Old November 12th, 2009, 10:43 PM posted to microsoft.public.excel.misc
Bob I
external usenet poster
 
Posts: 10,698
Default Something is not right

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.




  #3  
Old November 12th, 2009, 10:53 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Something is not right

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


  #5  
Old November 14th, 2009, 01:08 PM posted to microsoft.public.excel.misc
Jerry W. Lewis
external usenet poster
 
Posts: 491
Default Something is not right

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.

....
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:18 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 OfficeFrustration.
The comments are property of their posters.