1

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?

OutThere
  • 467
  • 2
  • 8
  • 19
  • 3
    This is the way the round function works. Until the fifth decimal (inclusive) it rounds down and over it it rounds up. How would you like it to work? Where to be the limit between the two situations? – FaneDuru May 12 '20 at 21:26
  • 1
    @FaneDuru with Bankers rounding([which vba uses](http://dailydoseofexcel.com/archives/2010/07/01/vba-rounding/)) half way should always round to the even number. – Scott Craner May 12 '20 at 22:32
  • You may want to see the link in [this question](https://stackoverflow.com/questions/311696/why-does-net-use-bankers-rounding-as-default). and .. [this](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function) – Naresh May 13 '20 at 04:14
  • @Scott Craner: Ups... I (only) now see that `Round(1233.715, 2)` returns deferent against `WorksheetFunction.Round(1233.715, 2)`. So, the function has to be changed according to his wish... – FaneDuru May 13 '20 at 11:00
  • 1
    @ScottCraner Then shouldn't `1233.715` round to `1233.72`? I think maybe `1233.715` is being stored internally as `1233.7149999999`. After all, `123.715` rounds to `123.72` – Ron Rosenfeld May 13 '20 at 11:22

1 Answers1

4

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

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • This is very helpful but I find that I still get the same problem with other numbers. For example, with the revised function 7.68510 rounds to 7.69 and 7.6850 rounds to 7.68. – OutThere May 13 '20 at 16:48
  • @OutThere: But, it looks that the function respects the rule to round **half** to even, so, it does that only **in case of exact .5 decimal (half)**. In case of .51, this is not half and it will be rounded up. Do you have another definition of `Banker’s Rounding`? I must confess that I heard about this rounding way only now and I tried to understand its meaning and why it can be useful. About the meaning, I expressed an opinion above. About why it may be useful, I think, it better balances rounding errors which occur, anyhow, comparing with rounding done in only one direction. Up or down... – FaneDuru May 13 '20 at 17:27
  • 2
    @OutThere Huh. I think you misunderstand the process. Rounding replaces the given number with a value that is closest to it, given some constraint (eg number of decimals). The different schemes differ based on what should be done only if a given number is equidistant from the two constraints. In your instance of `7.68510` being rounded to two decimals, clearly `7.68510` is closer to `7.69` than it is to `7.68`. So being rounded to `7.69` is proper. However `7.685` is equidistant from `7.68` and `7.69`, and will be rounded according to the scheme being used. – Ron Rosenfeld May 13 '20 at 18:05
  • 1
    @FaneDuru Exactly. There's an interesting discussion of different rounding methods, and their advantages and disadvantages, on [Wikipedia](https://en.wikipedia.org/wiki/Rounding) – Ron Rosenfeld May 13 '20 at 18:13
  • What is vbaRound? I don't see that defined anywhere. – Jason Willett Jun 18 '21 at 20:23
  • @JasonWillett It refers to the original formula used by the OP which he referred to as `the VBA Round() function` – Ron Rosenfeld Jun 18 '21 at 20:45