3

I am finding that the VBA Round function returns the wrong result. I found a working alternative which is to use the Application.Round instead.

Why doesn't the VBA Round function work as expected?

Sub roundingResults()
    Range("A1") = 123456.705
    Debug.Print "Approach #1 " & Round(Range("A1").Value, 2)
    Debug.Print "Approach #2 " & Application.Round(Range("A1").Value, 2)    
End Sub

Output

Approach #1 123456.7
Approach #2 123456.71
Shawn H
  • 1,118
  • 3
  • 10
  • 24
  • See [HERE](http://mathforum.org/library/drmath/view/58972.html) for a better explanation, but the gist is that there are two ways to round number ending in 5. The 1st approach uses the toward even. So if you changed the number to `123456.715` both would return the `123456.72`. The second appraoch uses the 5 as the deliminator. – Scott Craner Apr 20 '16 at 21:07

1 Answers1

1

There is no entirely correct answer for how to round. The differences have to do with how to handle values that fall exactly on the midpoint. Application.Round will always round up in that instance. e.g. 0.5 will round to 1.0. VBA.Round will tie break to the closest even number 

1.5-->2   
2.5-->2  
3.5-->4

and so forth. This is one way of several ways of reducing the bias inherent in always rounding up.

Also, if interested, there is a fairly extensive discussion of Rounding in this Wikipedia article

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Beat me by 20 seconds. – Scott Craner Apr 20 '16 at 21:07
  • 1
    @ScottCraner :-) Rounding is an interesting topic for me. There are many different ways of tiebreaking in addition to those used in the Excel and VBA functions. – Ron Rosenfeld Apr 20 '16 at 21:15
  • Additional info. VBA is using banker's rounding http://www.xbeat.net/vbspeed/i_BankersRounding.htm. Same method in .net http://stackoverflow.com/questions/311696/why-does-net-use-bankers-rounding-as-default – Jules Apr 21 '16 at 00:28