3

I am trying to reproduce some Visual Basic code in python. I have a function which produces a fraction (double) and converts it to an integer as follows:

Dim random_int As Integer
Dim rounded_int As Integer
random_int = CInt(Math.Ceiling(Rnd() * n)) + 1
rounded_int = CInt(random_int/2)

CInt() rounds double values to the nearest integer. However when the value is 0.5, this appears to be random; sometimes up, sometimes down. This makes the code difficult to test. In python(2.7), the number is always rounded in the same direction.

I could add or subtract a small number to the result (e.g. 0.1) but the original code should not changed.

Is this behaviour normal or can I control it in some way?

chriskelly
  • 7,526
  • 3
  • 32
  • 50
  • 3
    from the online manual page: "When the fractional part of a value is exactly 0.5, the CInt function rounds to the closest even number. For example, 0.5 rounds to 0, 1.5 rounds to 2, and 3.5 rounds to 4. The purpose of rounding to the closest even number is to compensate for a bias that could accumulate when many numbers are added together." http://msdn.microsoft.com/en-us/library/fctcwhw9%28v=vs.84%29.aspx Hardly Random! – Mitch Wheat Dec 10 '14 at 23:22
  • Thanks Mitch. Coming from a python program I had a lot of trouble figuring out why 2 apparently identical programs were not producing the same output. At first it seemed to me that vb was doing something wrong but the VB implementation makes a lot more sense. – chriskelly Dec 11 '14 at 09:41

1 Answers1

1

CInt in VBA uses Banker's Rounding, also called 'round half to even'. This explains the 'random' behavior. Some examples are:

2.5 -> 2
3.5 -> 4

You cannot customize the behavior using CInt. Historically, rounding method implementations are inconsistent among Microsoft's various technologies, this KB article illustrates this and provides code samples for various other common forms of rounding.

Another addition: due to floating point technical limitations, 1.5 might be represented as 1.4999999 internally, causing even further confusion. This article tries to explain why this happens and how to solve it.

Edit: I assumed Visual Basic for Applications because of the vba tag, if you are using Visual Basic .NET you can invoke Math.Round with a MidpointRounding overload.

Bas
  • 26,772
  • 8
  • 53
  • 86
  • I have removed the vba tag (visual basic does not have a tag so I used that instead). I guess Bankers Rounding also applies to VB. Nice simple and clear explanation. Thanks! – chriskelly Dec 10 '14 at 23:34
  • 1
    @chriskelly you can use the `vb.net` tag – Bas Dec 10 '14 at 23:48