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

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 2

My theory is VBA/VB6 is using x87 for floating point calculations and this implicitly converts doubles to higher precision if 80 bits for intermediate results. So an assignment to v or explicit cast with CDbl converts intermediate 80-bits value back to 64-bits effectivly rounding it (or truncating it).

Here is some discussion:

Extended (80-bit) double floating point in x87, not SSE2 - we don't miss it?

Answer 3

Both what Kevin and Jonathan have said are true, but Jonathan's answer is more applicable here. If you were dealing with Currency-type numbers instead of floating point, then the Banker's rounding rule would be applied.

Answer 4

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 5

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.