Single vs Double
 

Hi,

I have the code

Sub Test()
Dim X as Single
Dim Y as DOuble
Dim Z as Double

x=.54
Y=300
z=X*Y

End Sub()

Z should equal 162, but it is 162.000006437302

Why is that? - Does that mean that all the variables have to be double to
get the exact answer?

Version = Excel 2003

Thanks for your help

Single vs Double
 

Jeff wrote...
I have the code

Sub Test()
Dim X as Single
Dim Y as DOuble
Dim Z as Double

x=.54
Y=300
z=X*Y

End Sub()

Z should equal 162, but it is 162.000006437302

Why is that? . . .

Classic floating point rounding error. 0.54 can't be represented
exactly as a binary fraction.

. . . Does that mean that all the variables have to be double to
get the exact answer?

Not necessarily. If you had declared z as a single, z would have wound
up with the exact value 162 fortuitously.

When using floating point values, you need to check for APPROXIMATE
equality rather than exact equality, e.g.,

Abs(z - 162) < 0.5

rather than

z = 162

This has always been so, and it'll continue to be so for finite
precision floating point.

Single vs Double
 

To elaborate slightly on Harlan's correct response, the approximations to
0.54 a
0.540000021457672119140625 (Single)
0.540000000000000035527136788005009293556213378906 25 (Double)
Neither is exact. Either could result in surprises down the road, but you
are less likely to be surprised using Double.

Jerry

"Jeff" wrote:

Hi,

I have the code

Sub Test()
Dim X as Single
Dim Y as DOuble
Dim Z as Double

x=.54
Y=300
z=X*Y

End Sub()

Z should equal 162, but it is 162.000006437302

Why is that? - Does that mean that all the variables have to be double to
get the exact answer?

Version = Excel 2003

Thanks for your help

Single vs Double
 

Normally the error that you describe is insignificant and does not affect the
overall precision of the calculations materially. If however you are doing
some kind of iterative manipulation where the errors will compound to the
point where they materially effect the outcome of the calculation then you
can use the technique of keeping all of your variable of type long. Do all of
the manipulations of the numbers and as a last step divide the final output
by 100 or 1000 or whatever order of magnitude required. This can be handy for
financial applications where you need great precision to the penny but
nothing beyond the penny.
--
HTH...

Jim Thomlinson


"Harlan Grove" wrote:

Jeff wrote...
I have the code

Sub Test()
Dim X as Single
Dim Y as DOuble
Dim Z as Double

x=.54
Y=300
z=X*Y

End Sub()

Z should equal 162, but it is 162.000006437302

Why is that? . . .

Classic floating point rounding error. 0.54 can't be represented
exactly as a binary fraction.

. . . Does that mean that all the variables have to be double to
get the exact answer?

Not necessarily. If you had declared z as a single, z would have wound
up with the exact value 162 fortuitously.

When using floating point values, you need to check for APPROXIMATE
equality rather than exact equality, e.g.,

Abs(z - 162) < 0.5

rather than

z = 162

This has always been so, and it'll continue to be so for finite
precision floating point.