4

Something was causing unexpected results on my company long ago, and I found this problem:

Dim k As Double
Dim r As Integer
k = 182.5
r = Round(k,0)

The result of r is 182, this caused problems on my company and now i have to fix it.

The thing is that i observed this:

Dim k As Double
Dim r As Integer
k = 186.5
r = Round(k,0)

r = 187

When the unities of the integer part of the double are bigger than five, Round does what i expect, but it doesn't for unities <=5.

How can i solve this problem? Is there another function to get the right rounding?

lyrqs
  • 78
  • 1
  • 8

2 Answers2

5

This is called bankers rounding and attempts to distribute rounding up/down on .5 based on whether or not the nearest number is odd or even.

To round up on .5:

cint(format(182.5, "#0")) ''183
cint(format(186.5, "#0")) ''187
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • One should note, however, that Format$ converts the double into a String, from which it has to be converted back into an Int. This is very inefficient. Format (without the $ sign) is even worse, because it produces a Variant. –  Nov 11 '16 at 13:54
1

Well, first off, it is not so as you say.

k = 182.5
r = Round(k, 0)

will indeed produce 182, but

k = 186.5
r = Round(k, 0)

will produce 186, not 187 as you mention. Now,

k = 185.5
r = Round(k, 0)

will yield 186 as well. This is called Banker's Rounding and is the standard in VB6. The intention is to undo the bias of always rounding an exact middle up.

If you want to always round 0.5 up, use

k = 186.5
r = Int(k * 2 + 1) \ 2

If you want to always round 0.5 down, you can use something along the line of

k = 186.5
r = Int(k * 2 + 0.99) \ 2

Add as many 9s as there are significant digits after the decimal point.