Error with > operator (0.2 > 0.2??)

[Excel_VBA_QNA]

Can anyone explain the following behavior?


If (4/20) > (1-0.8) then
'VBA executes this code based on 0.2 (Variant/Double) > 0.2 (Variant/Double)
End If

Try it for yourself...

[6StringJazzer]

Floating point arithmetic done by a computer is complicated. As you know, computers store numbers in binary format. A real number is stored as a sign, base and exponent. As you learned as a youngster, in base 10 arithmetic, some numbers cannot be represented exactly in base 10 digits. For example, 1/3 gives a repeating decimal of

0.3...

You have the same situation with binary numbers. The value 4/20 is exactly 0.2 in base 10, but gives the repeating binary

0.0011...

I suspect that the answers to 4/20 and 1-0.8, although they yield the same number when done in base 10, give two slightly different answers in the least significant binary digit when rounded for storage on a computer, due to how they are computed. Representing the constant 0.8 then subtracting it from 1 is a completely different machine operation than a division.

I have only had to actually worry about this twice, once when I was writing software for NASA to acquire satellite signals, and the other when I was teaching Ada which has a rigorous specification for machine representation of numbers. Both were decades ago so I'm a little rusty.

[Excel_VBA_QNA]

I'm sure you're exactly right, 6StringJazzer. What bothers me is that the VBA IDE shows both of these values as "0.2". If the outcome of these calculations are different, why isn't one of these values something like 0.2000000...1 or 0.199999...999 to the limit of Double precision?

In any case, problem solved by applying Round() to both sides of the comparison.

[6StringJazzer]

Glad you resolved your issue, that is the textbook method.

Why does it show both values as 0.2 but doesn't treat them that way internally? This paper may give some insight (small PDF file).

[Excel_VBA_QNA]

It does at that... thank you.