round to even calculation

[sandbagger]

Function br(x)
br = Round(x, 2)
End Function

I thought the vba function "round" by default rounds to even so why I am getting these results when i call br?

unrounded-->rounded
4.405-->4.40
4.415-->4.42
4.425-->4.42
4.435-->4.43 (instead of 4.44)
4.445-->4.44
4.455-->4.45 (instead of 4.46)
4.465-->4.46
4.475-->4.47 (instead of 4.48)
4.485-->4.48
4.495-->4.49 (instead of 4.50)

[Kenneth Hobs]

The rounding function in VBA uses what I call old school rounding or some say bankers rounding. I prefer rounding up if the digit to right of precision is >= 5. What you want I suspect is Worksheetfunction.MRound() to round to a multiple of a part.

VBA Round() <> Worksheetfunction.Round() <> Worksheetfuction.MRound()


Function Round1(x As Double) As Double Round1 = Round(x, 2)
End Function


Function Round2(x As Double) As Double
Round2 = WorksheetFunction.Round(x, 2)
End Function


Function Round3(x As Variant) As Double
Round3 = WorksheetFunction.MRound(x, 0.02)
End Function

[Paul_Hossler]

I always thought that anything <= .500000000000000000000000 went down and anything > .500000000000000000000000 went up





?round(4.435000000001,2)
4.44

?round(4.435000000000,2)
4.43

[Kenneth Hobs]

Thanks for pointing that out Paul. One has to be careful as floating point issues can gum up common logic using mathematical principles.

My normal preference is Round2(), to round >=5 to right up of precision and <5 down. There are some bizarre rounding methods. I have to some in my Engineering work on occasion.

[Aflatoon]

The VBA round function does indeed round 0.5 to the nearest even number, but due to floating point issues, 4.435 for example is not seen as truly ending in 5. If you try it with 6.435 or 2.435 then you get exactly the banker's rounding you would expect:

?round(2.435000000000,2)
2.44
?round(6.435000000000,2)
6.44