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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|