Why do VBA and Excel floating point results differ?
 

In Excel 2003, I compute the following, with the
indicated results.

A1: =(1.4434 + 1.4434 + 1.4434) / 3
B1: =3*(1.4434 - A1)^2 / 2
C1: =(1.4434 - A1)^2

A1: 1.44340000000000E+00
B1: 7.39557098644699E-32
C1: 4.93038065763132E-32

I can understand why B1 and C1 are not zero.
I am very familiar with the vagaries of IEEE 754
representation.

But I do not understand why the results are
different when I make the same computations in
VBA. Can anyone offer some insight?

A2: =myavg()
B2: =myvar()
C2: =mydiff()
D2: =(1.4434 - A2)^2

A2: 1.44340000000000E+00
B2: 0.00000000000000E+00
C2: 0.00000000000000E+00
D2: 0.00000000000000E+00

The VBA functions a

Function myavg() As Double
myavg = (1.4434 + 1.4434 + 1.4434) / 3
End Function

Function myvar() As Double
Dim avg As Double
avg = (1.4434 + 1.4434 + 1.4434) / 3
myvar = 3 * (1.4434 - avg) ^ 2 / 2
End Function

Function mydiff() As Double
Dim avg As Double
avg = (1.4434 + 1.4434 + 1.4434) / 3
mydiff = (1.4434 - avg) ^ 2
End Function

Why do VBA and Excel floating point results differ?
 

Why do VBA and Excel floating point results differ?
 

"Martin Brown" wrote:
Compiled code and working variables stay on the 80 bit
floating point stack.

Ding! Thanks.

I did not think the VBA compiler would be that sophisticated.
But I believe the following confirms your assertion.

A1: =(1.4434 + 1.4434 + 1.4434) / 3
B1: =myavg()
C1: =myavg2()
D1: =(A1-B1)^4
E1: =(A1-C1)^4

A1: 1.44340000000000E+00
B1: 1.44340000000000E+00
C1: 1.44340000000000E+00
D1: 2.43086534291451E-63
E1: 0.00000000000000E+00

Note the small difference between A1 and myavg() in
D1, and no difference between A1 and myavg2() in E1.

myavg2() attempts to thwart any FP optimization as
follows.

Function myavg2() As Double
myavg2 = myavg3(1.4434, 1)
End Function

Function myavg3(x As Double, cnt As Integer)
If (cnt = 3) Then myavg3 = x / 3 _
Else myavg3 = myavg3(x + 1.4434, cnt + 1)
End Function

Why do VBA and Excel floating point results differ?
 

A more direct confirmation of Martin's claim is

Sub tryit()
Dim x As Double
x = 2# ^ 1023
MsgBox x * x / x
End Sub

If the expression x*x/x were not evaluated in 10-byte registers, the
calculation would necessarily overflow. It only works with inline
expressions involving explicitly declared Double's and using only the basic 4
arithmetic operations. For instance, if x were declared As Variant, or if
the expression were x^2/x, then the expression would overflow.

Question: Does this work on non-Intel processors under Windows or on a Mac?

Jerry

" wrote:

"Martin Brown" wrote:
Compiled code and working variables stay on the 80 bit
floating point stack.

Ding! Thanks.

I did not think the VBA compiler would be that sophisticated.
But I believe the following confirms your assertion.

A1: =(1.4434 + 1.4434 + 1.4434) / 3
B1: =myavg()
C1: =myavg2()
D1: =(A1-B1)^4
E1: =(A1-C1)^4

A1: 1.44340000000000E+00
B1: 1.44340000000000E+00
C1: 1.44340000000000E+00
D1: 2.43086534291451E-63
E1: 0.00000000000000E+00

Note the small difference between A1 and myavg() in
D1, and no difference between A1 and myavg2() in E1.

myavg2() attempts to thwart any FP optimization as
follows.

Function myavg2() As Double
myavg2 = myavg3(1.4434, 1)
End Function

Function myavg3(x As Double, cnt As Integer)
If (cnt = 3) Then myavg3 = x / 3 _
Else myavg3 = myavg3(x + 1.4434, cnt + 1)
End Function

Why do VBA and Excel floating point results differ?
 

"Jerry W. Lewis" wrote:
A more direct confirmation of Martin's claim is
Sub tryit()
Dim x As Double
x = 2# ^ 1023
MsgBox x * x / x
End Sub
If the expression x*x/x were not evaluated in 10-byte
registers, the calculation would necessarily overflow.

That demonstrates that VBA __sometimes__ relies on the 80-bit
FP registers. I wanted to confirm that that explains why my
computation of the average, ergo the variance and standard
deviation, is "exact" to 64 bits, whereas the computation by
AVERAGE() is not. To do that, I believe my recursive example
(or any other way to thwart VBA optimization) was necessary
to prove that point. This is what judges refer to as an argument
"on point".

It only works with inline expressions involving explicitly
declared Double's and using only the basic 4 arithmetic
operations.

Thanks for the insight.

Question: Does this work on non-Intel processors under
Windows or on a Mac?

Since the (2006) Mac uses Intel processors, the question is
moot :-). Just kidding ....

A Google search provides some answers or hints. But since
I cannot confirm the information, I don't want to "speculate"
here.