Why does CLng produce different results?

Question

Here's a little gem directly from my VBE (MS Excel 2007 VBA):

?clng(150*0.85)
 127 
x = 150*0.85
?clng(x)
 128 

Can anybody explain this behaviour? IMHO the first expression should yield 128 (.5 rounded to nearest even), or at least should both results be equal.

Answer 1

I think wqw is right, but I'll give the details.

In the statement clng(150 * 0.85), 150 * 0.85 is calculated in extended-precision:

150 = 1.001011 x 2^7

0.85 in double precision =

1.1011001100110011001100110011001100110011001100110011 x 2^-1

Multiply these by hand and you get

1.1111110111111111111111111111111111111111111111111111110001 x 2^6 =
127.4999999999999966693309261245303787291049957275390625

That's 59 bits, which fits comfortably in extended-precision. It's less than 127.5 so rounds down.

In the statement x = 150 * 0.85, that 59 bit value is rounded to 53 bits, giving

1.1111111 x 2^6 = 1111111.1 = 127.5

So it rounds up according to round-half-to-even.

(See my article http://www.exploringbinary.com/when-doubles-dont-behave-like-doubles/ for more information.)

Answer 2

Ahh one of the “fun” things about VBA is the rounding on CInt() etc is what is called bankers rounding. Bankers rounding is where 0.5 values are rounded up or down depending on if the number is an even number so 2.5 rounds to 2, 3.5 to 4 and so on.

More can be found here about rounding

http://www.consultdmw.com/rounding-numbers.htm

Answer 3

This is a bit of a guess, but .85 may not be representable as a floating point number. If it is off by 0.0000000000001 it can still affect rounding in weird ways.

If you use CDec(.85) to force it into decimal mode you don't get that weirdness. This is one of the many reasons why I don't use single/double where accuracy is important.