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.

Weird. `CLng(CDbl(150*0.85))` fixes it. `Round(150*0.85)` too. — wqw, Feb 14 '11 at 08:28
So does CSng. There goes my theory that it was a floating point error. — Jonathan Allen, Feb 14 '11 at 08:35

score 12 · Accepted Answer · edited Jul 24 '14 at 18:17

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.)

score 2 · Answer 2 · answered Feb 14 '11 at 08:25

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

answered Feb 14 '11 at 08:25

Kevin Ross

7,185
2
21
27

He mentions that it should be rounded to the nearest even in the question. – Jonathan Allen Feb 14 '11 at 08:31
In this case 127 is not the "nearest even". This explains nothing. – wqw Feb 14 '11 at 13:01

score 1 · Answer 3 · answered Feb 14 '11 at 08:34

1

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.

answered Feb 14 '11 at 08:34

Jonathan Allen

68,373
70
259
447

The first result could come from roundoff error (.4999... instead of .5), but in this case the second result should be 127 as well. In my understanding the in-between assignment to x should not affect the result of the cast. – Torben Klein Feb 14 '11 at 12:26

score 1 · Answer 4 · edited May 23 '17 at 11:52

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?

score 0 · Answer 5 · answered Feb 14 '11 at 08:43

0

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.

answered Feb 14 '11 at 08:43

Steve Jorgensen

11,725
1
33
43

CLng DOES apply Bankers rounding – Charles Williams Feb 14 '11 at 18:54
1

Yes, but that doesn't matter if what it's applying the rounding to is already a binary floating point number that includes a rounding error with respect to the decimal representation. The number will already be slightly above or below the 1/2 way point, so whatever rule the rounding algorithm would use to resolve 1/2-way values will never be applied. – Steve Jorgensen Feb 14 '11 at 19:07

Why does CLng produce different results?

5 Answers5

Linked