Why does Bankers Rounding function in Excel VBA give different results depending on number of decimal places?

Question

I'm trying to use the VBA Round() function in a spreadsheet using this handy code I found elsewhere in this forum:

Function BankerRound(rng As Double, sig As Integer) As Double
    BankerRound = Round(rng, sig)
End Function

But I'm surprised by these results (all using 2 decimal places)

1233.71537501667 rounds to 1233.72
1233.715 rounds to 1233.71

Could someone explain why the results are different?

Answer 1

Rounding algorithms differ on how they handle the "middle" cases. See Wikipedia discussion for more information.

With regard to so-called Bankers Rounding (which I don't believe is used by Bankers), the algorithm calls for rounding to the nearest even number.

So your particular example of 1233.715 should round to 1233.72, much the same way that 123.715 rounds to 123.72.

I believe the reason it does not is probably due to how MS VBA handles decimal values that cannot be represented exactly in accord with the IEEE specifications. Most likely, the decimal representation of the value being stored internally is something like 1233.71499999...

If this is important, you should be able to obtain the desired values by using the Decimal data type, which are stored as integers and not floating point binary.

For example:

Function bankerRound(num As Variant, numDecimals As Long)
    bankerRound = Round(CDec(num), numDecimals)
End Function

For further clarity (?confusion?) examine the results below:

the vbaRound function uses the Double data type