C
Cai Bing Lin
ROUND(((2226-123.36-39.34-303)-1600)*0.05, 2) == 8.01
G
Gord Dibben
No.
If you increase the DP to about 10 DP without the ROUND Function you will see
the answer is 8.014999999999900
ROUND rounds it up to 8.01
Gord Dibben MS Excel MVP
If you increase the DP to about 10 DP without the ROUND Function you will see
the answer is 8.014999999999900
ROUND rounds it up to 8.01
Gord Dibben MS Excel MVP
C
Cai Bing Lin
thank you .
But when I use VBA. It will be 8.02? why?
To 13 decimal places without the Round function
(((2226-123.36-39.34-303)-1600)*0.05) is 8.0150000000000
But when I use VBA. It will be 8.02? why?
To 13 decimal places without the Round function
(((2226-123.36-39.34-303)-1600)*0.05) is 8.0150000000000
J
joeu2004
Not a bug; just the banalities of binary arithmetic.
The short answer: it appears that VBA reorders the computation, which
results in a small difference in the binary result. You can
accomplish the same binary result in Excel with the following
reordered formula: ((2226-303-1600)-(123.36+39.34))*0.05.
Rearranging the order of computation changes the relative size of
pairs of operands in the binary operations, subtraction and addition.
This can cause infinitessimal differences in intermediate results.
In Excel, (((2226-123.36-39.34-303)-1600)*0.05) results in the
following 64-bit floating-point representation (borrowing from VBA
syntax): &h402007ce147ce140. The exact decimal value is
8.0149999999999863575794734060764312744140625.
The VBA computation results in the binary value &h402007ce147ce148.
The only difference is in the last 4 bits, specifically bit 3. It
makes an infinitessimal difference of about 1.421E-14. The exact
decimal value is 8.0150000000000005684341886080801486968994140625.
The Excel formula ((2226-303-1600)-(123.36+39.34))*0.05 has exactly
the same binary result as the VBA computation.
By the way, the Excel formula ((2226-303-1600) - 123.36 - 39.34)*0.05,
while mathematically equivalent, has a slightly different binary
result, namely &h402007ce147ce147. But Excel will also display that
as 8.01500000000000. The exact decimal value is
8.0149999999999987920773492078296840190887451171875.
In the first "8.0149..." value, 8.0149...986 (16 digits) rounds to
8.0149...9 (15 digits). In the second "8.0149..." value, 8.0149...998
(16 digits) rounds to 8.0150...0 (15 digits).
HTH.
J
Jerry W. Lewis
joeu2004 said:
I think you are mistaken about VBA reordering the computation. Otherwise, I
agree completely with your explanation.
I have never seen evidence that VBA reorders computations. However, VBA
results are consistent with VBA using 10-byte register arithmetic when
performing in-line 4-function calculations involving only explicitly Double
values. In the two small sub's below, the only difference is whether the
variables are explicitly Double, or are Variants that contain Double values.
If VBA attempted to optimize calculations by reordering, wouldn't it treat
the expression the same in both sub's?
The first sub produces a result that agrees both with Excel and with 8-byte
binary evaluation in the order given. The second sub produces a result that
agrees with both VBA without variables and with 8-byte reporting of 10-byte
binary evaluation (in the order given) from 8-byte inputs.
Jerry
Sub try1()
a = 2226
b = 123.36
c = 39.34
d = 303
e = 1600
f = 0.05
MsgBox ((a - b - c - d) - e) * f
End Sub
Sub try2()
Dim a As Double, b As Double, c As Double, d As Double, e As Double, f
As Double
a = 2226
b = 123.36
c = 39.34
d = 303
e = 1600
f = 0.05
MsgBox ((a - b - c - d) - e) * f
End Sub
J
joeu2004
Yeah, I was uncomfortable with my conclusion, as well, especially
since it required moving the 1600 across parentheses. But I know of
one compiler that will do that for constants per se, not variables. I
was going to try using variables this morning, but your posting
obviating the need for me to do so.
Interesting. That was my first speculation. But there was no way
that I could prove or disprove it, nor prove or disprove that Excel
does not rely on the FPU register file. Moreover, I was not sure if
the present-day Intel PC CPUs still have an 80-bit FPU register file,
introduced in the 8087 in "the good old days". I know that the
Itanium FPU has a 128-bit register file. Also, I did not know if all
Intel-compatible CPUs (notably AMD) have the same width FPU register
file -- although I would think they do just to avoid any hint of
incompatibility. Finally, when I discovered that reordering led to
the exact same binary result, I thought it was unlikely that 80-bit
floating-processing would have the same binary result as well. (But I
agree now that I am probably wrong.)
I do agree that the two subs demonstrate the differences in the binary
results.
I do not know enough (anything!) about the vagaries of the Variant
type to say with impunity that VBA will treat both examples the same
-- although I would be surprised if it didn't. Besides, I would be
doubly surprised if VBA reordered variable expressions; not because it
cannot, but because it is well-known that programmers use parenthesis
to try to control evaluation order, even when it does not matter based
on precedence order alone.
I do believe that VBA recognize integral numbers differently. When I
append "#" to 123.36, the VBE removes it; not so for 2226. But the
Variant example behaved the same even when I use explicit Double
integral contants (e.g. 2226#).
Finally, I am suspicious of examples that use variables immediately
after assigning constants to them. I know of one optimizing compiler
that avoids the variable assigment and treats their values as
constants in such simple examples. But I was careful to pass the
constants to functions, thereby eliminating any possibility of
optimization. Again, the results were the same.
I agree that this is a better conclusion, given the experiments with
variables. I am still surprised that the binary results are identical
with reordering. In the Excel examples, it makes me wonder if the
explanation for the difference is something other than simply
reordering. "Who knows what evil lurks in the hearts of man" ;-)
J
joeu2004
Errata....
Well, maybe my recollection is wrong. According to a Wikipedia
article, the Itanium FPU register file is 82 [sic] bits wide. Of
course, Wiki articles are not dispositive, and I'm in too much of a
hurry to search Intel online documentation. I tossed my own copies
only recently. Sigh, I will write 10,000 times "I will not throw away
'useless' documentation".
My point was: I was sure if the present-day Intel PC CPUs (Pentium
family, etc) still use 80-bit register files. I defer to Jerry's
knowledge that they do.
Well, maybe my recollection is wrong. According to a Wikipedia
article, the Itanium FPU register file is 82 [sic] bits wide. Of
course, Wiki articles are not dispositive, and I'm in too much of a
hurry to search Intel online documentation. I tossed my own copies
only recently. Sigh, I will write 10,000 times "I will not throw away
'useless' documentation".
My point was: I was sure if the present-day Intel PC CPUs (Pentium
family, etc) still use 80-bit register files. I defer to Jerry's
knowledge that they do.
J
joeu2004
Errata^2....
Aarrgghh! "I was __not__ sure ...". Does anyone know how to
implement a "NOTs Lock" keyboard macro? It would insert the word
"not" wherever it belongs automagically
.
Aarrgghh! "I was __not__ sure ...". Does anyone know how to
implement a "NOTs Lock" keyboard macro? It would insert the word
"not" wherever it belongs automagically
.
J
joeu2004
I tend to agree, as I noted in my previous response. And just to
solidify Jerry's conclusion, here is a VBA counter-example using
Double, whose result matches the OP's Excel result.
The difference is: this example forces the FPU result (80-bit or
whatever) from each binary operation to be truncated to a 64-bit
intermediate value, as Jerry asserts that Excel does. (Fortunately,
VBA does not optimize the intermediate assignments out of existence ;-
Sorry for the misdirection regarding reordering. But I hope it might
be instructive for other questions about binary arithmetic results.
Initially, I was surprised by the difference between the two
reordering examples: a-(b+c) v. a-b-c. The difference in scale
factors between operands of intermediate results is not that great --
or so I thought at first.
Function dtestit3(arg) As Double
If arg Then
dtestit3 = dtestit4(2226#, 123.36, 39.34, 303#, 1600#, 0.05)
Else
dtestit3 = dtestit5(2226#, 123.36, 39.34, 303#, 1600#, 0.05)
End If
End Function
' let VBA optimize 80-bit(?) FP register usage for intermediate
results
Private Function dtestit4(a As Double, b As Double, _
c As Double, d As Double, e As Double, f As Double) As Double
dtestit4 = ((a - b - c - d) - e) * f
End Function
' force VBA to truncate intermediate results in 80-bit(?) FP registers
' into 64-bit values
Private Function dtestit5(a As Double, b As Double, _
c As Double, d As Double, e As Double, f As Double) As Double
Dim temp As Double
temp = a - b
temp = temp - c
temp = temp - d
temp = temp - e
temp = temp * f
dtestit5 = temp
End Function
C
CaiBingLin
Thank you all very much.
joeu2004 said: