Re: Serious rounding error?
From: Rick Rothstein (rickNOSPAMnews_at_NOSPAMcomcast.net)
Date: Wed, 25 Feb 2004 22:00:11 -0500
> Dim a As Long
> ' a is 0
> a = 1 / 2
> ' a is 2???
> a = 1 + 1 / 2
> ' a is 0
> a = CLng(1 / 2)
> ' a is 2???
> a = CLng(1 + 1 / 2)
My standard response to this type of question...
Rick - MVP
It is not a "bug", it is the way VB was designed to work. It uses something
known as Banker's rounding which, if the number ends in exactly 5 and you
want to round to the position in front of the 5, it rounds numbers down if
the number in front of the 5's position is even and rounds up otherwise. It
is supposed to protect against repeated calculation using rounded numbers so
that answer aren't always biased upward. For more on this issue than you
probably want to know, see this link
In any event, I agree with you -- it is not what normally would be expected.
Also beware of CInt, Clng and *almost* any other VB function that performs
rounding. The one exception -- Format. In place of
you can use for the general case
Format$(YourNumber, "0." & String$(Precision, "0"))
which looks somewhat intimidating. But note the simpler format in specific
cases. For example,
You could also use straight math as follows (this always works):
Sgn(Number) * Int(0.5 + Abs(Number) * _
10 ^ Precision) / 10 ^ Precision
which always rounds 5's (or greater) away from zero. That is 123.45
and -123.45 become 123.5 and -123.5 respectively. If you always want to
round upward (that is -123.45 becomes -123.4 which is larger than 123.45;
123.45 still becomes 123.5), then use this instead
Int(0.5 + Number * 10 ^ Precision) / 10 ^ Precision
Although the documentation for it is not as prominent as it should be, you
can find out about Banker's Rounding in the Remarks section of the Type
Conversion Functions entry in VB's help files. (And while it seems to deal
only with rounding from one decimal place to an exact integer, it actually
deals with any number whose decimal part ends in 5 which is being rounded up
one decimal place.) Here is the quote from that section:
"When the fractional part is exactly 0.5, CInt and CLng always round it to
the nearest even number. For example, 0.5 rounds to 0, and 1.5 rounds to 2.
CInt and CLng differ from the Fix and Int functions, which truncate, rather
than round, the fractional part of a number. Also, Fix and Int always return
a value of the same type as is passed in."