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

wrote:

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?

In the Excel version you are storing the intermediate results in 64 bit
floating point representation and there is no exact binary
representation for 1.4434. Try 1.125 instead.

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

Compiled code and working variables stay on the 80 bit floating point
stack. The extra guard digits in the tempreal mantissa are protecting
you from the rounding errors inherent in the IEEE FP specification.

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

Regards,
Martin Brown

"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

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

"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.