Single vs Double

[Guest]

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

 

[Harlan Grove]

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.

 

[Guest]

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

Jerry

 

[Guest]

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.